I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserNameWe are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join '
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>>I have two queries joined with a union, one query by itself takes 34ms to
>> complete and the other runs by itself in 340ms but when they are joined
>> by
>> the union (or a union all) the combined query takes an incredible one
>> minute
>> and 15 seconds. Why does the union com with such an incredible cost?
>> The query is:
>> select
>> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
>> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
>> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
>> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
>> from Team..Users U with (nolock)
>> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
>> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
>> and
>> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
>> IsNumeric(Substring(U.Name,2,5))=1
>> union all
>> select
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.Emplid
>> else null
>> end as UserID
>> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
>> from vwPS_Employees E with (nolock)
>> left join vwTeamUsers T with (nolock) on
>> Case Len(E.EmplID)
>> When 6 then E.EmplID
>> When 5 then 'C' + E.EmplID
>> end = T.[Name] collate database_default
>> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
>> Len(E.EmplID) in (5,6) and T.[ID] is not null
>> Order by UserName
>sql
Showing posts with label killer. Show all posts
Showing posts with label killer. Show all posts
Monday, March 26, 2012
Killer Union
I have two queries joined with a union, one query by itself takes 34ms to
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserName
We are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>
|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join ?
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>
complete and the other runs by itself in 340ms but when they are joined by
the union (or a union all) the combined query takes an incredible one minute
and 15 seconds. Why does the union com with such an incredible cost?
The query is:
select
U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
SQL_Latin1_General_CP1_CI_AS + ')' as UserName
from Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0 and
Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
IsNumeric(Substring(U.Name,2,5))=1
union all
select
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name] collate database_default
where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
Len(E.EmplID) in (5,6) and T.[ID] is not null
Order by UserName
We are not really going to be able to tell why, unless we can see the view
statements, structure of base tables, query plans, etc. I do have a couple
of questions though... why all the collate clauses? Why not let the front
end deal with parentheses, concatenation, etc.? Why left join with
vwTeamPartners and then make it an inner join by including it in the where
clause? Why left join with vwTeamUsers and then make it an inner join by
including it in the where clause?
"Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>I have two queries joined with a union, one query by itself takes 34ms to
> complete and the other runs by itself in 340ms but when they are joined
> by
> the union (or a union all) the combined query takes an incredible one
> minute
> and 15 seconds. Why does the union com with such an incredible cost?
> The query is:
> select
> U.[Name] COLLATE SQL_Latin1_General_CP1_CI_AS as UserID
> ,U.LastName COLLATE SQL_Latin1_General_CP1_CI_AS + ', ' + U.FirstName
> COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + U.MiddleName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ' (' + P.PartnerName COLLATE
> SQL_Latin1_General_CP1_CI_AS + ')' as UserName
> from Team..Users U with (nolock)
> Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
> where P.UserID is not null and Len(U.Name) = 6 and Len(U.FirstName) > 0
> and
> Len(U.LastName) > 0 and Lower(Substring(U.Name,1,1)) ='v' and
> IsNumeric(Substring(U.Name,2,5))=1
> union all
> select
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.Emplid
> else null
> end as UserID
> ,E.Full_Name + ' (' + E.DeptID + ')' as UserName
> from vwPS_Employees E with (nolock)
> left join vwTeamUsers T with (nolock) on
> Case Len(E.EmplID)
> When 6 then E.EmplID
> When 5 then 'C' + E.EmplID
> end = T.[Name] collate database_default
> where E.Empl_Status in('A','P','L','S') and E.DeptID <> '000' and
> Len(E.EmplID) in (5,6) and T.[ID] is not null
> Order by UserName
>
|||UNIONS and anything but Inner joins are always expensive.
There is alwasy a better way to do it, as long as you are using stored
procedures as the method of access.
If you are not, then you have bigger problems
The biggest issue is that both selects have to complete in entirity before
the union can begin.
Things I noticed about your Query:
Your Collates are in series in the same column of the select.
Only the last one would count, and it is the default for SQL.
They should be omitted.
The only time Collate is normally seen is when you have different
collations in the return from multiple linked servers.
Performance Hit 2 )
always specify the schema, Database..Table Only works if the only schema
is dbo.
It forces QA to check the sys.objects table for table ownership and
access
WAIT WAIT WAIT
Your using a case statment in a join ?
Your Joining to Views, I bet they are well written as this one.
Did you put indexes on your views.
If we are Left joining P but P.userid can't be null, THAT's AN Inner
I understand now this is an example of how to get a 3 minute execution on
2 tables with 2 rows of data each.
Hire A DBA
SELECT
U.[Name] as UserID
, U.LastName + ', ' + U.FirstName + ' ' + U.MiddleName + ' (' +
P.PartnerName + ')' as UserName
FROM
Team..Users U with (nolock)
Left Join vwTeamPartners P with (nolock) on U.ID = P.UserID
WHERE
P.UserID is not null
and Len(U.Name) = 6
and Len(U.FirstName) = 0
and Len(U.LastName)=0
and Lower(Substring(U.Name,1,1)) ='v'
and IsNumeric(Substring(U.Name,2,5))=1
UNION ALL
SELECT
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.Emplid
else null
end as UserID
,E.Full_Name + ' (' + E.DeptID + ')' as UserName
from
vwPS_Employees E with (nolock)
left join vwTeamUsers T with (nolock) on
Case Len(E.EmplID)
When 6 then E.EmplID
When 5 then 'C' + E.EmplID
end = T.[Name]
where
E.Empl_Status in('A','P','L','S')
and E.DeptID < '000'
and Len(E.EmplID) in (5,6)
and T.[ID] is not null
Order by
UserName
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:5E67B352-323C-4433-8126-F7C3B4A5FC17@.microsoft.com...
> We are not really going to be able to tell why, unless we can see the view
> statements, structure of base tables, query plans, etc. I do have a
> couple of questions though... why all the collate clauses? Why not let
> the front end deal with parentheses, concatenation, etc.? Why left join
> with vwTeamPartners and then make it an inner join by including it in the
> where clause? Why left join with vwTeamUsers and then make it an inner
> join by including it in the where clause?
>
> "Roy Sinclair" <RoySinclair@.discussions.microsoft.com> wrote in message
> news:53062774-BF60-415F-9043-33DEE1EC07EC@.microsoft.com...
>
Killer scripts
I am trying to understand how to monitor SQL Server for various performance
problems and would like to find some scripts to cause performance problems.
For example:
A script causing high CPU usage
A script causing high memory usage
A script causing intensive IO
Can anyone point me in the right direction or give me an idea of what kind
of sql I could write to produce the above effects.The 'min server memory' and 'max server memory' configuration settings can
be used to restrict the amount of RAM available to SQL Server. Bumping this
down to 50 - 100mb or so would not only impact memory, but could affect disk
I/O and CPU due to disk swapping.
set transaction isolation level SERIALIZABLE or specifying the HOLDLOCK
option on a query will cuase a dataset to be exclusively locked.
The WAITFOR DELAY 'hh:mm:ss' statement can be used to pause processing in
the middle of a transaction, thus holding a lock on resources.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:35D5D71C-688D-4EBA-A3B5-0C9B8117F01C@.microsoft.com...
> I am trying to understand how to monitor SQL Server for various
performance
> problems and would like to find some scripts to cause performance
problems.
> For example:
> A script causing high CPU usage
> A script causing high memory usage
> A script causing intensive IO
> Can anyone point me in the right direction or give me an idea of what kind
> of sql I could write to produce the above effects.|||This may do..
========================================
===============
Create Procedure ResourceHog As
Set NoCount On
Declare @.i int
Set @.i = 0
Create Table #hog ( num int )
While (1=1)
Begin
While (@.i < 1000)
Begin
Insert #hog Values (@.i)
Select @.i = @.i + 1
End
Select h1.num n1, h2.num n2, h3.num n3, h4.num n4, h5.num n5
From #hog h1
Full Outer Join #hog h2 On h1.num <> h2.num
Full Outer Join #hog h3 On h1.num <> h3.num
Full Outer Join #hog h4 On h1.num <> h4.num
Full Outer Join #hog h5 On h1.num <> h5.num
End
========================================
===============
problems and would like to find some scripts to cause performance problems.
For example:
A script causing high CPU usage
A script causing high memory usage
A script causing intensive IO
Can anyone point me in the right direction or give me an idea of what kind
of sql I could write to produce the above effects.The 'min server memory' and 'max server memory' configuration settings can
be used to restrict the amount of RAM available to SQL Server. Bumping this
down to 50 - 100mb or so would not only impact memory, but could affect disk
I/O and CPU due to disk swapping.
set transaction isolation level SERIALIZABLE or specifying the HOLDLOCK
option on a query will cuase a dataset to be exclusively locked.
The WAITFOR DELAY 'hh:mm:ss' statement can be used to pause processing in
the middle of a transaction, thus holding a lock on resources.
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:35D5D71C-688D-4EBA-A3B5-0C9B8117F01C@.microsoft.com...
> I am trying to understand how to monitor SQL Server for various
performance
> problems and would like to find some scripts to cause performance
problems.
> For example:
> A script causing high CPU usage
> A script causing high memory usage
> A script causing intensive IO
> Can anyone point me in the right direction or give me an idea of what kind
> of sql I could write to produce the above effects.|||This may do..
========================================
===============
Create Procedure ResourceHog As
Set NoCount On
Declare @.i int
Set @.i = 0
Create Table #hog ( num int )
While (1=1)
Begin
While (@.i < 1000)
Begin
Insert #hog Values (@.i)
Select @.i = @.i + 1
End
Select h1.num n1, h2.num n2, h3.num n3, h4.num n4, h5.num n5
From #hog h1
Full Outer Join #hog h2 On h1.num <> h2.num
Full Outer Join #hog h3 On h1.num <> h3.num
Full Outer Join #hog h4 On h1.num <> h4.num
Full Outer Join #hog h5 On h1.num <> h5.num
End
========================================
===============
killer query - limit usage ?
how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>|||very good point Andrew, sorry for being a little dim.
i read something about a READ ONLY option 2 which is supposed to speed up
queries.
Thanks for your time, great help
Scott|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>|||very good point Andrew, sorry for being a little dim.
i read something about a READ ONLY option 2 which is supposed to speed up
queries.
Thanks for your time, great help
Scott|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
--
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>
killer query - limit usage ?
Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>
That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>
|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.
|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>
|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>
That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>
|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.
|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>
|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>
killer query - limit usage ?
how do i limit SQL servers CPU usage ? (2005 express)
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>sql
killer queries are slowing all other services.
mem limited already.
thanks
scottThe best thing to do is optimise the queries, use Profiler , identify the
slow ones and optimise
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Scott" <s@.yahoo.co.uk> wrote in message
news:O$WwN0wtHHA.292@.TK2MSFTNGP02.phx.gbl...
> how do i limit SQL servers CPU usage ? (2005 express)
> killer queries are slowing all other services.
> mem limited already.
> thanks
> scott
>|||how do i ID the problematic queries ?
thanks for reponse
scott|||must be a way to limit cpu server side too !
too much data is the prob.|||Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.c...nce_audit10.asp
Performance Audit
http://www.microsoft.com/technet/pr...perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>|||thanks very much for response.
all the best.
scott|||quick question. How can query optimization help my query:
select * from [table]
when my table has 5 million records.
surly the only way to sort this is to limit the cpu ?
sorry to keep posting
scott|||That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:
>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>sql
Killer problem with Scheduled Reports
I'm running on SQL 2000 with SRS. Standard install (I think, it was all set up by IT)
I have reports that run fine in the Report Manager but when they are run through a schedule (email) some of them (albeit most) "rsProcessingAborted" because of a report error.
Looking at the logs it seems generally based around comparison failure because of datatypes, eg (from the logs):
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of sort expression for the list 'List1' cannot be performed. The comparison failed. Please check the data type returned by sort expression. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: The processing of group expression for the table 'tblGroupTable' cannot be performed. The comparison failed. Please check the data type returned by group expression.Now, when I remove sorting/grouping the reports run through the schedule OK; but that is obviously NOT what I want the report to look like. But it proves that the sorting/grouping has something to do with the problem.
I'm pretty sure that they are NOT the problem though, as the reports render in Report Manager fine with all the original sorting/grouping in place (ie no problems during development, just when we wanted them served to the employees. Isn't that always the way it goes?). We've even had some reports which scheduled OK previously, but throw errors now.
I'm at a loss!!
Could it be a time-out issue somewhere? If so: where? Looking at the Execution Logs, the reports that fail have unusually long Process Times, with a Data Retrieval Time of 0. I guess that the process time is longer because of processing the error, but does the Data Retrieval time of 0 mean that no data was delivered to the report? How can that be? IT tell me it can't be memory issues as it's sitting on a 2GB machine, but could there be something else with SQL set-up? I googled the error and found a few pages where people said that puting SRS in its own application pool fixed their problem. When I passed this on to IT they said that would only help memory... Any ideas?Any suggestions greatfully appreciated
Do the report subscriptions ALWAYS fail, or intermittently?
Do those same reports ALWAYS succeed when executed live?
How much RAM is on the Dev machine, and what OS?
What OS is on the productio machine?
What changed between the time that some scheduled reports worked fine, and when they started erroring?
Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.
|||Also, what are the types and #'s of CPU's on the Dev and Production machines?|||Mike Schetterer -- MSFT wrote:
Do the report subscriptions ALWAYS fail, or intermittently?
The reports seem to fail always when they contain some data. Maybe the oposite is better: The reports don't fail when they do not contain data. Of the report I can remember off the top of my head: the schedule didn't fail on 4 occasions, three of those didn't have any data, one had one record.
For example, for one of our reports that is meant to run this morning:
Time Start
Time End
Data Retrieval
Process
Render
Status
User Name
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=3
6/02/2006 7:00:08 AM
6/02/2006 7:00:09 AM
0
112
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=8
6/02/2006 7:00:08 AM
6/02/2006 7:00:12 AM
2979
359
255
rsSuccess
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=6
6/02/2006 7:00:57 AM
6/02/2006 7:00:58 AM
0
47
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=11
6/02/2006 7:00:57 AM
6/02/2006 7:00:58 AM
0
60
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=13
6/02/2006 7:00:58 AM
6/02/2006 7:00:58 AM
0
51
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=1
6/02/2006 7:00:58 AM
6/02/2006 7:00:58 AM
0
72
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=9
6/02/2006 7:00:59 AM
6/02/2006 7:00:59 AM
0
63
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
pmtStartDate=01/01/2000 00:00:00
pmtEndDate=02/06/2006 00:00:00
Team=10
6/02/2006 7:00:59 AM
6/02/2006 7:00:59 AM
0
48
0
rsProcessingAborted
NT AUTHORITY\NETWORK SERVICE
Do those same reports ALWAYS succeed when executed live?
YES!
How much RAM is on the Dev machine, and what OS?
Intel Xeon 2.4Gb x 2, 2Gb Ram
The Dev machine and the production machine are the same. I use a Remote Desktop into the production machine from my desktop, I guess the documents are stored on my workstation and deployed to the production machine.
What OS is on the productio machine?
Windows Server 2003, SP1
What changed between the time that some scheduled reports worked fine, and when they started erroring?
Nothing that I am aware of... but I'll ask the IT people...
Removing the grouping/sorting may have alleviated the load on the processing engine enough so that the report succeeded.
The schedules fail regardless of the time of day they run (I wondered if the server got overloaded whilst trying to do other things): but there is no difference.
Another interesting fact: whilst investigating I discovered that one of the reports that ran through a schedule after all grouping and sorting was removed displayed "#error" in a cell that contained the result from a Library I'd written. Though this report runs and displays fine through the Report Manager! (this field was not used in the grouping or sorting either).
Yes, I've read somewhere else that how you've configured the Application Pool can make a difference (our IT said it wouldn't here). But I've definitely read in a different forum that that fixed their problem.
At the moment the Report Server Interface and the Report Server are in the DefaultAppPool.
Thanks for your interest!
Perry
|||Do your reports have parameters?
When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?
Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?
Thanks,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Do your reports have parameters?
Some do, others don't, but it doesn't seem to be a problem.
When you create your subscription - are the parameters set staticly or are the based on a query. If they're query based - can you try running the query and selecting some of the value combinations you receive in return through the UI to see if they work?
Both, or sometimes all three (included type in)
Yes, ALL the reports run fine through the Report Manager.
Your comment above about report executions failing when there is data, but succeeding when there is no data - is it true to say that whenever there is data the subscription fails, or just some of the time?
No: they fail most of the time. In the section of the ExecutionLog in a previous post you can see that for that report it ran once (with data) and failed the rest (they would have had data too.
Thanks,
Perry
|||What are the group and sort expressions from the report that you find is failing?
-Lukasz
|||What are the group and sort expressions from the report that you find is failing?
In every case it's "=Fields!FieldName.value"
Perry
Subscribe to:
Posts (Atom)