SQL Server Performance Forum – Threads Archive

Where do I start – Performance Quest

Sometimes, the server on which my DB resides starts slowing down. At that point, the CPU is at constant 100%. After a while (usually 2-4 hours later), it all start working great.
The application we have is a DB intense app. Here are a few observations I have: * When I look at the number of users connected (when server is slow and fast) is around 100 (sp_who ‘app name’).
* Some of the same queries that take about 1-3 seconds take about 3 minutes when everything is slow.
* I have checked the indexes and they all seem to be OK (I run dbcc ShowContig and then dbcc reIndex if needed)
* I update the table statistics of a couple of tables regularly that are heavily accessed (insert/updates).
* The DB has 2 processors and 2GB ram and the current DB size is 1.5GB I am not sure what else I should be looking when things slow down? Any tips are greatly appreciated. Thanks for your time.

Sounds like you are having lock problems. Try run sp_lock with sp_who2 when everything slow down and see if the problem is there.
if so there are different things you can do to improve performance (different choice of indexes, using lock hints), depending on the query you are running.

Also, get a Profiler trace of this activity, and see what specific queries are causing the problem, and take a look at each one in depth, seeing what you can do to speed them up. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589 – to troubleshoot Slow-Running Queries . http://www.sql-server-performance.com/q&a95.asp – Q&A for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

We had similar problems for about two weeks. And it used to happen everyday between 11AM and 1PM. And perfectly normal behaviour before 11AM and after 1PM. And during the two hours all queries used to take 10 times longer than what it used to during other times. So even with the profiler trace I could not tell which one(s) were causing the problem. Luckly for me I started looking at the releases that were done in the last month and found out that a program was with a query that was changed recently this query is a very commonly run query (atleast 10 times a minute) and was accessing 300,000 records with a composite index. But unfortunately it was doing a scan on the index. And the reason why it used to go down between 11 and 1 is becuase of the high usage. Hope this helps!!
Regards,
Ravi

You should also trace from network point of view and see any other process is running during that time. Event viewer logs, sQL server error log will be starting point to view for any information.
What is the significance of that query? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>