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 ***********************