Performace Monitor and SQL Profiler

Hi,
I was running Performace Monitor and SQL Profiler to keep track of
changes on the server for period of a week. And for some reason this ONE
table keeps getting locked out. The third party vendor suggested that SQL
Profiler creates locks again the users database.
I though the SQL Profiler acquire any locks against user databases, it is
getting information form SQL API directly?
Also the performace counters I read had no affect on the SQL Server...
Is my understanding wrong? Can someone clearify it for me? Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005Well... I'm not sure if SQL Profiler may cause locks or not however
Microsoft recommends using these kind of tools (SQL Profiler, Performance
Monitor) from another machine. Because they effect performance on the
machine they work. However, in this case they also recommend using a
dedicated connection between those two machines to avoid network traffic
because of using these tools if possible.
So, in your case, yes, they have affect on the SQL Server somehow.
--
Ekrem Ã?nsoy
"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> Hi,
> I was running Performace Monitor and SQL Profiler to keep track of
> changes on the server for period of a week. And for some reason this ONE
> table keeps getting locked out. The third party vendor suggested that SQL
> Profiler creates locks again the users database.
> I though the SQL Profiler acquire any locks against user databases, it is
> getting information form SQL API directly?
> Also the performace counters I read had no affect on the SQL Server...
> Is my understanding wrong? Can someone clearify it for me? Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005|||I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem Ã?nsoy" wrote:
> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem Ã?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > Hi,
> >
> > I was running Performace Monitor and SQL Profiler to keep track of
> > changes on the server for period of a week. And for some reason this ONE
> > table keeps getting locked out. The third party vendor suggested that SQL
> > Profiler creates locks again the users database.
> >
> > I though the SQL Profiler acquire any locks against user databases, it is
> > getting information form SQL API directly?
> >
> > Also the performace counters I read had no affect on the SQL Server...
> >
> > Is my understanding wrong? Can someone clearify it for me? Thanks!
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
>|||Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem Ã?nsoy" wrote:
> > Well... I'm not sure if SQL Profiler may cause locks or not however
> > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > Monitor) from another machine. Because they effect performance on the
> > machine they work. However, in this case they also recommend using a
> > dedicated connection between those two machines to avoid network traffic
> > because of using these tools if possible.
> >
> > So, in your case, yes, they have affect on the SQL Server somehow.
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > Hi,
> > >
> > > I was running Performace Monitor and SQL Profiler to keep track of
> > > changes on the server for period of a week. And for some reason this ONE
> > > table keeps getting locked out. The third party vendor suggested that SQL
> > > Profiler creates locks again the users database.
> > >
> > > I though the SQL Profiler acquire any locks against user databases, it is
> > > getting information form SQL API directly?
> > >
> > > Also the performace counters I read had no affect on the SQL Server...
> > >
> > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > >
> > > --
> > > Mohit K. Gupta
> > > B.Sc. CS, Minor Japanese
> > > MCTS: SQL Server 2005
> >|||Thanks Guys ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:
> > I see, I will have to check that out on if it has any affect on user database
> > or not. But today I did a trace that logged about 250,000 records on
> > another database, and that server didn't notice any deadlocks or lock
> > timeouts. But I did take your advice and this time ran the SQL Monitor on a
> > different computer. So maybe that had some affect.
> >
> > So I do suspect me running it on the serer caused locks but might have
> > caused some slow down. So I think the application is either timing out too
> > quickly or something else is a miss. Thank-you for your reply.
> >
> > --
> > Mohit K. Gupta
> > B.Sc. CS, Minor Japanese
> > MCTS: SQL Server 2005
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> > > Well... I'm not sure if SQL Profiler may cause locks or not however
> > > Microsoft recommends using these kind of tools (SQL Profiler, Performance
> > > Monitor) from another machine. Because they effect performance on the
> > > machine they work. However, in this case they also recommend using a
> > > dedicated connection between those two machines to avoid network traffic
> > > because of using these tools if possible.
> > >
> > > So, in your case, yes, they have affect on the SQL Server somehow.
> > >
> > > --
> > > Ekrem Ã?nsoy
> > >
> > >
> > >
> > > "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> > > news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
> > > > Hi,
> > > >
> > > > I was running Performace Monitor and SQL Profiler to keep track of
> > > > changes on the server for period of a week. And for some reason this ONE
> > > > table keeps getting locked out. The third party vendor suggested that SQL
> > > > Profiler creates locks again the users database.
> > > >
> > > > I though the SQL Profiler acquire any locks against user databases, it is
> > > > getting information form SQL API directly?
> > > >
> > > > Also the performace counters I read had no affect on the SQL Server...
> > > >
> > > > Is my understanding wrong? Can someone clearify it for me? Thanks!
> > > >
> > > > --
> > > > Mohit K. Gupta
> > > > B.Sc. CS, Minor Japanese
> > > > MCTS: SQL Server 2005
> > >

PERCENTILE_CONT equivalent in MS SQL

Hi All,

I have a table with a count of 30K rows that describe values over a period of time. I would like to be able to replicate the PERCENTILE_CONT function that is found in Oracle with MS SQL Server 2005 but have not had much success.

Would anybody know of a way to use current MS SQL syntax to retrieve the value of the 5th,10th,15th,20th etc... percentile of my table values? Assume that all of the values are associated with the same key. Oracle gives you the Percentile_Cont function for inverse percentile calculation but what does SQL2005 give you?

Sample Table Structure:

ID int, Timestamp datetime, Value int

There is no equivalent aggregate function in TSQL. You can however use the query described below which is same as the PERCENTILE_CONT implementation in Oracle. The ORDER BY clause in the PERCENTILE_CONT goes in the ROW_NUMBER OVER clause in TSQL query and the GROUP BY clause becomes the partition by clause in the TSQL query. I have kept the parameter to percentile_cont as variable. You can use the TSQL query and rewrite using Oracle syntax to check also.

-- Given the Oracle example query using sample EMP table:

SELECT deptno,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal DESC) as percent_cont
FROM emp
GROUP BY deptno;

-- Oracle equivalent using analytic functions present in SQL Server

select deptno, sum(case grp
when 1 then (case PRN when RN then sal end)
else (case PRN when FRN then (CRN - RN) * sal when CRN then (RN - FRN) * sal end)
end) as percent_cont
from (
select deptno, sal, N, PRN, RN, CRN, FRN, case when e3.CRN = e3.FRN and e3.CRN = e3.RN then 1 else 0 end as grp
from (
select deptno, sal, N, PRN, RN, CEIL(RN) as CRN, FLOOR(RN) as FRN
from (
select deptno, sal, PRN, N, 1 + (.5* (N-1)) as RN
from (
select deptno, sal, row_number() over(partition by deptno order by sal desc) as PRN, count(*) over(partition by deptno) as N
from emp
) e1
) e2
) e3
) er
group by deptno;
-- TSQL equivalent:

-- Optimized version that requires only one scan of the data

-- Same as above except that this uses CTE instead of derived table
declare @.p float;
set @.p = .5;
with e1
as
(
select deptno, sal, row_number() over(partition by deptno order by sal desc) as PRN, count(*) over(partition by deptno) as N
from emp
),
e2
as
(
select deptno, sal, PRN, N, 1 + (@.p * (N-1)) as RN
from e1
),
e3
as
(
select deptno, sal, N, PRN, RN, CEILING(RN) as CRN, FLOOR(RN) as FRN
from e2
),
er
as
(
select deptno, sal, N, PRN, RN, CRN, FRN, case when e3.CRN = e3.FRN and e3.CRN = e3.RN then 1 else 0 end as grp
from e3
)
select deptno, sum(case grp
when 1 then (case PRN when RN then sal end)
else (case PRN when FRN then (CRN - RN) * sal when CRN then (RN - FRN) * sal end)
end) as percent_cont
from er
group by deptno;
go

For those who are interested, I blogged about this at link below. You can download a script that creates the Oracle demo tables in SQL Server and run the queries.

http://blogs.msdn.com/sqltips/archive/2006/05/15/598372.aspx

|||Thank you very much. That is exactly what I was looking for.