| Author |
Topic |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2005-11-12 : 07:14:57
|
| Guys, Just wondering, if you have a sql server that have CPU running at around 70% (up to 90% sometimes) one day, where it normally runs at around 20-30%, nothing had really change for the past few weeks. From profiler, I can see a lot of calls to 1 store proc which is all using index seek (so it should be very fast), but it took over 3 sec to complete (some even up to 10sec), and its doing a lot of reads (400k avg), is there any other ways to speed it up? as once I stop the front end server (iis server), the CPU comes back down to like 0%, so its not the server itself having issue. Any ideas?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-12 : 08:53:04
|
| Database MDF file being extended at the time? Particularly an issue if you have a >1GB database and it is set to the default expansion of 10% - i.e. 100MB per expansion.This crucifies our SQL servers that support IIS web applications because the number of SQL connections sky rockets, and impatient users start pressing RETRY etc.If you are using Profiler etc. to debug this scenario it will be characterised by the number of connections to the DB jumping (to more than four-fold of normal)Kristen |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-12 : 11:02:45
|
| Is the SQL Server and IIS on the same server?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-12 : 11:48:50
|
| Not in our case, no ... well, on our DEV boxes perhaps ...Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-12 : 21:56:39
|
 I was talking to Westley. I realize you wouldn't do that if you had a choice Kristen.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-14 : 21:11:34
|
"I was talking to Westley"Thought so ...  Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-11-16 : 19:27:35
|
| Thanks guys, for more info, the DB is not doing the autogrow as its been disable, the db still got a lot of free space for it to growth (and huge log space), so space is not an issue, and no, IIS is not on the same server. Guess my question is, what normally cause a high Reads? will that be likely the HDD issue? but its already using a SAN (not too good in SAN myself) but i guess it should provide high reads and writes. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-16 : 21:19:13
|
| Have you tried reindexing or updating the statistics? Have you tried running the index tuning wizard? You might be doing index seeks and still getting lots of reads. Can you post the execution plan?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-11-17 : 01:20:37
|
| hmm, i haven't try reindex and update stats, but i'm asking in general, if u have a query that do more Reads and its all doing index seek, is there ways to speed it up? Apart from new HDD etc that is. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-17 : 20:31:33
|
| It depends where the reads are coming from, what the data is, and about a billion other factors. You might be doing bookmark lookups because there are no covering indexes. You might have etc. etc. etc. etc. General questions are hard to get specific answers on because they are general.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-11-17 : 21:26:44
|
| hmm, thanks anyway, i'll try to get more info, as they don't complain about this issue anymore, and I forgot which proc that is after the weekends :)but I remember its not doing any bookmark lookups at all, (or it might have but using less then 10% in the plan)thanks for all the info anyway :) |
|
|
|
|
Performance issue