One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>sql
Showing posts with label sp4. Show all posts
Showing posts with label sp4. Show all posts
Wednesday, March 21, 2012
Performance Problems - HELP, PLEASE
One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> One of the prime functions for this server is Report services.
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>> One of my SQL2000 sp3 servers has seems to be responding slower.
>> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual
>> processors.
>> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
>> on the C drive is the MASTER, there are no master files on the E drive.
>> One of the prime functions for this server is Report services.
>> I began looking around after one of the report writers mentioned that
>> things were responding slower then in the past, I run this query >
>> use master
>> select * From sysdatabases
>> I get this line for Master>
>> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
>> 00:00:00.000 0 80
>> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
>> If I use EM and look at Properties for the Master DB it shows the data
>> file where it should be>
>> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
>> I then ran
>> use master
>> dbcc checkdb ('master')
>> and there were no errors , the final line read like this>
>> "CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'master'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator."
>> Any and ALL recommendations and suggestions would be most appreciated!!!
>>
>
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> One of the prime functions for this server is Report services.
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>> One of my SQL2000 sp3 servers has seems to be responding slower.
>> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual
>> processors.
>> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
>> on the C drive is the MASTER, there are no master files on the E drive.
>> One of the prime functions for this server is Report services.
>> I began looking around after one of the report writers mentioned that
>> things were responding slower then in the past, I run this query >
>> use master
>> select * From sysdatabases
>> I get this line for Master>
>> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
>> 00:00:00.000 0 80
>> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
>> If I use EM and look at Properties for the Master DB it shows the data
>> file where it should be>
>> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
>> I then ran
>> use master
>> dbcc checkdb ('master')
>> and there were no errors , the final line read like this>
>> "CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'master'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator."
>> Any and ALL recommendations and suggestions would be most appreciated!!!
>>
>
Tuesday, March 20, 2012
Performance problem after sql2k sp4 installed
We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performanc
e
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided i
n
the profiler but a don't se any of the underlying procedurecalls made in thi
s
job. Do you have any suggestions on how to set up the profiler to get hand o
n
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
>|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided
in
> the profiler but a don't se any of the underlying procedurecalls made in t
his
> job. Do you have any suggestions on how to set up the profiler to get hand
on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performanc
e
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided i
n
the profiler but a don't se any of the underlying procedurecalls made in thi
s
job. Do you have any suggestions on how to set up the profiler to get hand o
n
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
>|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided
in
> the profiler but a don't se any of the underlying procedurecalls made in t
his
> job. Do you have any suggestions on how to set up the profiler to get hand
on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>
Performance problem after sql2k sp4 installed
We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
Thanks
Hi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks
|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
Thanks
Hi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks
|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
Performance problem after sql2k sp4 installed
We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
> > We'w got major performance problems with inserting data into large tables
> > (more than 25 000 000 rows) after installing the latest sp. The performance
> > decreased with more than 90%.
> >
> > Our program takes one row at the time and creates inserts and updates for
> > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > transaction.
> >
> >
> > occassionaly we can capture an error during insert. The error is 1222.
> >
> >
> > Have anybody encounted the same problem an even got any solution.
> >
> > Thanks|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
--
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > 1222 = "Lock request time out period exceeded."
> >
> > Run profiler and see what is happening on your server during the loads.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Stefan W" wrote:
> >
> > > We'w got major performance problems with inserting data into large tables
> > > (more than 25 000 000 rows) after installing the latest sp. The performance
> > > decreased with more than 90%.
> > >
> > > Our program takes one row at the time and creates inserts and updates for
> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > > transaction.
> > >
> > >
> > > occassionaly we can capture an error during insert. The error is 1222.
> > >
> > >
> > > Have anybody encounted the same problem an even got any solution.
> > >
> > > Thanks|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>> Hi!
>> I've tried to do that, but i cant get information from the particular
>> job.
>> The job is desiged as a stored procedure that contains a loop that calls
>> other stored procedures. Iv'e tried using the different templates
>> provided in
>> the profiler but a don't se any of the underlying procedurecalls made in
>> this
>> job. Do you have any suggestions on how to set up the profiler to get
>> hand on
>> these jobs!
>>
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > 1222 = "Lock request time out period exceeded."
>> >
>> > Run profiler and see what is happening on your server during the loads.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "Stefan W" wrote:
>> >
>> > > We'w got major performance problems with inserting data into large
>> > > tables
>> > > (more than 25 000 000 rows) after installing the latest sp. The
>> > > performance
>> > > decreased with more than 90%.
>> > >
>> > > Our program takes one row at the time and creates inserts and updates
>> > > for
>> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
>> > > transaction.
>> > >
>> > >
>> > > occassionaly we can capture an error during insert. The error is
>> > > 1222.
>> > >
>> > >
>> > > Have anybody encounted the same problem an even got any solution.
>> > >
>> > > Thanks
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
> > We'w got major performance problems with inserting data into large tables
> > (more than 25 000 000 rows) after installing the latest sp. The performance
> > decreased with more than 90%.
> >
> > Our program takes one row at the time and creates inserts and updates for
> > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > transaction.
> >
> >
> > occassionaly we can capture an error during insert. The error is 1222.
> >
> >
> > Have anybody encounted the same problem an even got any solution.
> >
> > Thanks|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
--
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > 1222 = "Lock request time out period exceeded."
> >
> > Run profiler and see what is happening on your server during the loads.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Stefan W" wrote:
> >
> > > We'w got major performance problems with inserting data into large tables
> > > (more than 25 000 000 rows) after installing the latest sp. The performance
> > > decreased with more than 90%.
> > >
> > > Our program takes one row at the time and creates inserts and updates for
> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > > transaction.
> > >
> > >
> > > occassionaly we can capture an error during insert. The error is 1222.
> > >
> > >
> > > Have anybody encounted the same problem an even got any solution.
> > >
> > > Thanks|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>> Hi!
>> I've tried to do that, but i cant get information from the particular
>> job.
>> The job is desiged as a stored procedure that contains a loop that calls
>> other stored procedures. Iv'e tried using the different templates
>> provided in
>> the profiler but a don't se any of the underlying procedurecalls made in
>> this
>> job. Do you have any suggestions on how to set up the profiler to get
>> hand on
>> these jobs!
>>
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > 1222 = "Lock request time out period exceeded."
>> >
>> > Run profiler and see what is happening on your server during the loads.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "Stefan W" wrote:
>> >
>> > > We'w got major performance problems with inserting data into large
>> > > tables
>> > > (more than 25 000 000 rows) after installing the latest sp. The
>> > > performance
>> > > decreased with more than 90%.
>> > >
>> > > Our program takes one row at the time and creates inserts and updates
>> > > for
>> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
>> > > transaction.
>> > >
>> > >
>> > > occassionaly we can capture an error during insert. The error is
>> > > 1222.
>> > >
>> > >
>> > > Have anybody encounted the same problem an even got any solution.
>> > >
>> > > Thanks
Performance Problem - Request for advice
I am a DBA working for a large business in the North East of England.
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.
IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants
BTW, the number of users/connections seems pretty low.
My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,
Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?
The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.
..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.
Is someone starting up a games server on the box?
Cheers
Phil
--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.
IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants
BTW, the number of users/connections seems pretty low.
My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,
Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?
The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.
..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.
Is someone starting up a games server on the box?
Cheers
Phil
--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:
Performance problem - can't identify cause
Hi
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Monday, March 12, 2012
Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise
I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Friday, March 9, 2012
Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise
I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Saturday, February 25, 2012
Performance object "SQL Server" does not exist
Hi,
I am running SQL Server 2000 SP4 on a Windows Server 2003 64bit Edition. I indend to monitor the SQL Server performance in the Windows Performance Monitor however neither the SQL Server performance object nor the associated counters are visible.
Any idea why and how I possibly install it?
Thanks in advance,
Greg
When ever I've had the counters drop off either a SQL Server restart or an OS restart will resolve the issue.|||Thanks.
I did so however the counters haven't emerged after the restart of the both.
Any help would be appreciated.
Greg
|||Come on........! The suggestion to restart the servers shows little respect towards this issue.
Would somebody please help?
Thanks.
|||Is the SQL server a 64 bit edition?
THe issue is you can only see 64bit related counters and only way you load the 32bit version of PERFMON to be able to read the 32 bit SQL Server counters, that is easily done by running this: mmc /32 perfmon.msc.
|||mmc /32 perfmon.msc was the solution.
Thanks!
Monday, February 20, 2012
performance monitor counters
I have a windows 2000 sp4 server running sql 2000 sp3.
The server only has one drive - drive 'C' with 101G, used
space = 31.5g and free space = 70.1gigs.
I like to monitor the following counters in performance
monitor:
Memory: Available Bytes
Memory: Pages/sec
Process: Working Set
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB)
PhysicalDisk: % Disk Time
PhysicalDisk: Avg. Disk Queue Length
Processor:% Processor Time
System: %Total Processor Time
Processor: % Privileged Time
Processor: %User Time
System: Processor Queue Length
Which ranges are normal when the server is not performing
anything extensive?
I appreciate your help with this information because know
how to monitor a server is one thing - but know what is
too high or too low is another thing which I need help
with. Thank you.It depends. YOu need to have a normal baseline for your
systems to be able to tell what is abnormal for your
systems. If you never established such a thing, it's
educated guesses beyond that.
Some things are more absolute. For example, if you have
sustained disk queue lengths (usually above 3), you are
more than likely disk bound, or something funky is going
on (i.e. you may need to rebuild indexes ... but check
first).
Again, I can't stress enough that you need to know a
baseline before you go into production to determine what
is normal for you and what is not.
>--Original Message--
>I have a windows 2000 sp4 server running sql 2000 sp3.
>The server only has one drive - drive 'C' with 101G, used
>space = 31.5g and free space = 70.1gigs.
>I like to monitor the following counters in performance
>monitor:
>Memory: Available Bytes
>Memory: Pages/sec
>Process: Working Set
>SQL Server: Buffer Manager: Buffer Cache Hit Ratio
>SQL Server: Buffer Manager: Total Pages
>SQL Server: Memory Manager: Total Server Memory (KB)
>PhysicalDisk: % Disk Time
>PhysicalDisk: Avg. Disk Queue Length
>Processor:% Processor Time
>System: %Total Processor Time
>Processor: % Privileged Time
>Processor: %User Time
>System: Processor Queue Length
>Which ranges are normal when the server is not performing
>anything extensive?
>I appreciate your help with this information because know
>how to monitor a server is one thing - but know what is
>too high or too low is another thing which I need help
>with. Thank you.
>.
>|||Hi
You may want to invest in:
SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
Which talks about how to calculating/evaluating wether the values you have
are good or bad.
John
"Regina Styles" <anonymous@.discussions.microsoft.com> wrote in message
news:077b01c3d149$91ec8300$a601280a@.phx.gbl...
> I have a windows 2000 sp4 server running sql 2000 sp3.
> The server only has one drive - drive 'C' with 101G, used
> space = 31.5g and free space = 70.1gigs.
> I like to monitor the following counters in performance
> monitor:
> Memory: Available Bytes
> Memory: Pages/sec
> Process: Working Set
> SQL Server: Buffer Manager: Buffer Cache Hit Ratio
> SQL Server: Buffer Manager: Total Pages
> SQL Server: Memory Manager: Total Server Memory (KB)
> PhysicalDisk: % Disk Time
> PhysicalDisk: Avg. Disk Queue Length
> Processor:% Processor Time
> System: %Total Processor Time
> Processor: % Privileged Time
> Processor: %User Time
> System: Processor Queue Length
> Which ranges are normal when the server is not performing
> anything extensive?
> I appreciate your help with this information because know
> how to monitor a server is one thing - but know what is
> too high or too low is another thing which I need help
> with. Thank you.
>|||I appreciate your response. This is all in development
and I don't need to do anything in production. I am
really in a learning phase right now. Do you have an
average of what is good for a server with nothing running
other than sql and may be one application that is not
doing much to the databases at all?
Thanks.
>--Original Message--
>It depends. YOu need to have a normal baseline for your
>systems to be able to tell what is abnormal for your
>systems. If you never established such a thing, it's
>educated guesses beyond that.
>Some things are more absolute. For example, if you have
>sustained disk queue lengths (usually above 3), you are
>more than likely disk bound, or something funky is going
>on (i.e. you may need to rebuild indexes ... but check
>first).
>Again, I can't stress enough that you need to know a
>baseline before you go into production to determine what
>is normal for you and what is not.
>>--Original Message--
>>I have a windows 2000 sp4 server running sql 2000 sp3.
>>The server only has one drive - drive 'C' with 101G,
used
>>space = 31.5g and free space = 70.1gigs.
>>I like to monitor the following counters in performance
>>monitor:
>>Memory: Available Bytes
>>Memory: Pages/sec
>>Process: Working Set
>>SQL Server: Buffer Manager: Buffer Cache Hit Ratio
>>SQL Server: Buffer Manager: Total Pages
>>SQL Server: Memory Manager: Total Server Memory (KB)
>>PhysicalDisk: % Disk Time
>>PhysicalDisk: Avg. Disk Queue Length
>>Processor:% Processor Time
>>System: %Total Processor Time
>>Processor: % Privileged Time
>>Processor: %User Time
>>System: Processor Queue Length
>>Which ranges are normal when the server is not
performing
>>anything extensive?
>>I appreciate your help with this information because
know
>>how to monitor a server is one thing - but know what is
>>too high or too low is another thing which I need help
>>with. Thank you.
>>.
>.
>
The server only has one drive - drive 'C' with 101G, used
space = 31.5g and free space = 70.1gigs.
I like to monitor the following counters in performance
monitor:
Memory: Available Bytes
Memory: Pages/sec
Process: Working Set
SQL Server: Buffer Manager: Buffer Cache Hit Ratio
SQL Server: Buffer Manager: Total Pages
SQL Server: Memory Manager: Total Server Memory (KB)
PhysicalDisk: % Disk Time
PhysicalDisk: Avg. Disk Queue Length
Processor:% Processor Time
System: %Total Processor Time
Processor: % Privileged Time
Processor: %User Time
System: Processor Queue Length
Which ranges are normal when the server is not performing
anything extensive?
I appreciate your help with this information because know
how to monitor a server is one thing - but know what is
too high or too low is another thing which I need help
with. Thank you.It depends. YOu need to have a normal baseline for your
systems to be able to tell what is abnormal for your
systems. If you never established such a thing, it's
educated guesses beyond that.
Some things are more absolute. For example, if you have
sustained disk queue lengths (usually above 3), you are
more than likely disk bound, or something funky is going
on (i.e. you may need to rebuild indexes ... but check
first).
Again, I can't stress enough that you need to know a
baseline before you go into production to determine what
is normal for you and what is not.
>--Original Message--
>I have a windows 2000 sp4 server running sql 2000 sp3.
>The server only has one drive - drive 'C' with 101G, used
>space = 31.5g and free space = 70.1gigs.
>I like to monitor the following counters in performance
>monitor:
>Memory: Available Bytes
>Memory: Pages/sec
>Process: Working Set
>SQL Server: Buffer Manager: Buffer Cache Hit Ratio
>SQL Server: Buffer Manager: Total Pages
>SQL Server: Memory Manager: Total Server Memory (KB)
>PhysicalDisk: % Disk Time
>PhysicalDisk: Avg. Disk Queue Length
>Processor:% Processor Time
>System: %Total Processor Time
>Processor: % Privileged Time
>Processor: %User Time
>System: Processor Queue Length
>Which ranges are normal when the server is not performing
>anything extensive?
>I appreciate your help with this information because know
>how to monitor a server is one thing - but know what is
>too high or too low is another thing which I need help
>with. Thank you.
>.
>|||Hi
You may want to invest in:
SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
Which talks about how to calculating/evaluating wether the values you have
are good or bad.
John
"Regina Styles" <anonymous@.discussions.microsoft.com> wrote in message
news:077b01c3d149$91ec8300$a601280a@.phx.gbl...
> I have a windows 2000 sp4 server running sql 2000 sp3.
> The server only has one drive - drive 'C' with 101G, used
> space = 31.5g and free space = 70.1gigs.
> I like to monitor the following counters in performance
> monitor:
> Memory: Available Bytes
> Memory: Pages/sec
> Process: Working Set
> SQL Server: Buffer Manager: Buffer Cache Hit Ratio
> SQL Server: Buffer Manager: Total Pages
> SQL Server: Memory Manager: Total Server Memory (KB)
> PhysicalDisk: % Disk Time
> PhysicalDisk: Avg. Disk Queue Length
> Processor:% Processor Time
> System: %Total Processor Time
> Processor: % Privileged Time
> Processor: %User Time
> System: Processor Queue Length
> Which ranges are normal when the server is not performing
> anything extensive?
> I appreciate your help with this information because know
> how to monitor a server is one thing - but know what is
> too high or too low is another thing which I need help
> with. Thank you.
>|||I appreciate your response. This is all in development
and I don't need to do anything in production. I am
really in a learning phase right now. Do you have an
average of what is good for a server with nothing running
other than sql and may be one application that is not
doing much to the databases at all?
Thanks.
>--Original Message--
>It depends. YOu need to have a normal baseline for your
>systems to be able to tell what is abnormal for your
>systems. If you never established such a thing, it's
>educated guesses beyond that.
>Some things are more absolute. For example, if you have
>sustained disk queue lengths (usually above 3), you are
>more than likely disk bound, or something funky is going
>on (i.e. you may need to rebuild indexes ... but check
>first).
>Again, I can't stress enough that you need to know a
>baseline before you go into production to determine what
>is normal for you and what is not.
>>--Original Message--
>>I have a windows 2000 sp4 server running sql 2000 sp3.
>>The server only has one drive - drive 'C' with 101G,
used
>>space = 31.5g and free space = 70.1gigs.
>>I like to monitor the following counters in performance
>>monitor:
>>Memory: Available Bytes
>>Memory: Pages/sec
>>Process: Working Set
>>SQL Server: Buffer Manager: Buffer Cache Hit Ratio
>>SQL Server: Buffer Manager: Total Pages
>>SQL Server: Memory Manager: Total Server Memory (KB)
>>PhysicalDisk: % Disk Time
>>PhysicalDisk: Avg. Disk Queue Length
>>Processor:% Processor Time
>>System: %Total Processor Time
>>Processor: % Privileged Time
>>Processor: %User Time
>>System: Processor Queue Length
>>Which ranges are normal when the server is not
performing
>>anything extensive?
>>I appreciate your help with this information because
know
>>how to monitor a server is one thing - but know what is
>>too high or too low is another thing which I need help
>>with. Thank you.
>>.
>.
>
Subscribe to:
Posts (Atom)