| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-09-29 : 21:29:47
|
| Hi,We have a server that s too slow. The tables have about between 1 million and 3 million records each. We have about 30 tables for production. The other tables are mostly look up tables (just for personnel and categories and so on, only static tables)1/ do u guys think that s too much data, I mean about 3 millions records in a table. Because the manager said, may be if u archive some records and delete them from the tables, the system might become quick and performant?2/ When we check the activity monitor in management in entreprise manager, we find a process Id 55 that has the value 1 in the Blocking column, and other processes having that process ID 55 in the Blocked by column.Does the fact that the blocking column for a process equals 1 mean that the process is having a problem, and how do we know what causes the problem for sure?3/When we check Performance monitor, pls note that we have RAID5 with 5 disks, we see that Avg. Disk Queue Length counter ranging between 20 and 70 and especially if we run a query for example, although simple select query in one of the tables, the Avg. Disk Queue Length counter goes to the roof to 100 % in performance monitor.I don t think we have a pb with the CPU, it looks good, not going to the roof.For your info, we have the web server on that same SQL server machine as well, but the web server s working well, only sql server and the client application that accesses it is slow, the web pages are pretty fast.Am I following the good path to diagnose what s wrong. It s my first time doing this, and I am trying to learn.I appreciate your guidance. |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-30 : 05:31:17
|
| "the manager said, may be if u archive some records and delete them from the tables, the system might become quick and performant?"He's probably right, but you'll then just have the same problem when the system has grown a bit bigger. It may buy you some time, but you need to fix the performance problems."we have the web server on that same SQL server machine as well"We've had a few of those, they have always done less work that separate SQL and Web boxes supporting loads of sites which each do the same amount of work as the one site on the shared box (if you get my analogy).Kristen |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-30 : 19:25:09
|
| Looks like disk i/o caused slowness, but have to find out why get slow disk. How many disk arrays does the server have? How do you place db files on disk arrays? Does tempdb sit on its own disk array? How often do you rebuild index or update statistics in the db? |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-30 : 23:39:35
|
quote:
Originally posted by nr
>> do u guys think that s too much data,no.2.See http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlandhttp://www.nigelrivett.net/SQLAdmin/SaveQueryPlans.htmlThis is the thing to look at first - fortunately it will be pretty simple.It's the spid 55 that is doing the blocking.You just need to make sure it doesn't block for long if at all.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
I see the spid 55 with the value blocking equals 1 for long time. P.S: I will also ask about the other info guys and get back to you with more info because I am not really familiar with the terms Disk arrays and so on. I ll ask the admins. Thanks a lot I appreciate your coaching. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-10-01 : 00:12:41
|
| If you run the sp sp_nrinfo it should show you the command that the blocking spid is running an possibly where it is run from - and the command that is being blocked.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-05 : 09:20:13
|
quote:
Originally posted by rmiao
Looks like disk i/o caused slowness, but have to find out why get slow disk. How many disk arrays does the server have? How do you place db files on disk arrays? Does tempdb sit on its own disk array? How often do you rebuild index or update statistics in the db?
Hi,We have RAID 5 configuration. the data file is on a E$ drive that s split on the 5 disks(RAID5). The TempDB and the data DB are located exactly on the same location in the E$ drive.The TempDB seems to occupy a space of 305M. The production Database mdf file has 3G and its ldf file has: 2GThe statistics are on autoupdatestatistics is ON.For the index, I don t think they ever rebuild them. Is that a problem?I hope I answered the questions right.One more thing, when I monitor with: performace monitor, the Avg. Disk Queue Read Length is always in the roof, between 50 and 70 (we have RAID5).So what should I do next to rule out the problem?Also, my question is: if you find that the Avg. Disk Queue Read Length is high, does that mean that the problem is definitely in the hardware, menaing the hardware is causing the bad counter and therefore slowing down the sql server or could it be the opposite as well, meaning: the slow queries can make the Avg. Disk Queue Read Length go up and the that the hardware is mainly the scpegoat that we accuse although it s not his fault :)P.S: I didn t have a chance to run any script yet on the server, I m not allowed to do that yet, we also don t have sp3 on the server so we can t install the blcoking monitoring script either |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-05 : 11:27:53
|
| "For the index, I don t think they ever rebuild them. Is that a problem?"Probably. if the index has never been rebuilt it probably has an inefficient "shape", and possibly the Statistics have never been updated either, in which case SQL Server will be using it assuming that it is the "shape" it had on day one, whereas in fact its a different "shape" now that lots of data has been added / changed etc.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-06 : 00:07:53
|
| Auto update statistics db option only updates auto created statistics in the db, you have to set shceduled job or use maintenance plan to rebuild index and update statistics. |
|
|
|
|
I need help with a slow sql 2000 server