July 8, 2008 at 1:20 am
Hi
Any pointers or help greatfully received.
We have a very fast server running only SQL 2005.
Selects and general speed of one DB is being greatly effected by another DB. Some selects are taking as much as 5 times as long when the other DB is being accessed by an application. The application in question does not do anything untoward and is not very busy in itself.
The Server is hardly stressed in any of the usual places (CPU (15%), memory(1.7gb out of 8gb) etc)
We have done the usual things, checked SP's for long running processes, cross joins etc, but have found nothign that would indicate it would slow down another DB that it doesnt even access!
Any clues where we should be focusing on? ANy performance metrics we should be lookign at? What areas of MSSQL would effect multiple DB's at the same time?
Thanks for any input
G
July 8, 2008 at 3:48 am
Hav you checked your disks? Check the physical disk counter in perfmon.
Avg sec/read, avg sec/write % idle time.
Run profiler for a bit, catch the slow runnig queries. See if the queries are always slow, or eratically slow.
32 bit system, or 64? What's SQL's max memory set to?
What are do the following perfmon counters look like?
SQL Buffer manager: Buffer cache hit ratio
SQL Buffer manager: lazy writes/sec
SQL Buffer manager: checkpoint pages/sec
SQL Buffer manager: page life expectency
SQL Access methods: full scans/sec
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2008 at 6:36 am
Hi Gail, Thanks for the help: Here goes:
32 bit system 147483647 MB (default) (its a 2*Quad, 8gb raid 5 fyi)
Note: The server is in production so it is in use. But we did 4 tests as follows, with a counter rest after each one.
With the offending database/application running
Avg sec/read - 0.048
avg sec/write - 0.080
% idle time - 23%
SQL Buffer manager: Buffer cache hit ratio - 99.747
SQL Buffer manager: lazy writes/sec - 12
SQL Buffer manager: checkpoint pages/sec - 0.4
SQL Buffer manager: page life expectency - 29
SQL Access methods: full scans/sec - 210
With the offending database/application running AND a test SP that calls a number of others to simulate select load on the other database were having problems with
(this script took 415 seconds to run)
Avg sec/read - 0.077
avg sec/write - 0.045
% idle time - 1.236
SQL Buffer manager: Buffer cache hit ratio - 99.43
SQL Buffer manager: lazy writes/sec - 14.98
SQL Buffer manager: checkpoint pages/sec - 0.384
SQL Buffer manager: page life expectency - 77
SQL Access methods: full scans/sec - 153
With the offending database/application STOPPED
Avg sec/read - 0.015
avg sec/write - 0.008
% idle time - 80.3
SQL Buffer manager: Buffer cache hit ratio - 99.7
SQL Buffer manager: lazy writes/sec - 1.1
SQL Buffer manager: checkpoint pages/sec - 0.588
SQL Buffer manager: page life expectency - 156
SQL Access methods: full scans/sec - 142.3
With the offending database/application STOPPED but with a test SP that calls a number of others to simulate select load on the other database were having problems with.
(this script took 230 seconds to run)
Avg sec/read - 0.026
avg sec/write - 0.049
% idle time - 1.05
SQL Buffer manager: Buffer cache hit ratio - 99.03
SQL Buffer manager: lazy writes/sec - 0.0
SQL Buffer manager: checkpoint pages/sec - 0.0
SQL Buffer manager: page life expectency - 225
SQL Access methods: full scans/sec - 132
Thanks for your help, hope it makes more sense to you than me.
G
July 8, 2008 at 7:30 am
Firstly, you're on 32 bit. Unless you have AWE enabled and the /pae switch in boot.ini, SQL can only use 2 GB max. that would explain why so little memory is in use.
Your lazy writes are higher than your checkpoint pages, which is a very rough indication that you may have memory pressure.
Your disk stats don't look good. The recomendation for avg sec/read and avg sec/write is between 10 and 50 ms. yours is averaging 70-80 with the offending app running.
Disk idle time should ideally be somewhere above 80%. An idle % of 25 or lower indicates that your disks are getting hammered.
The full scans/sec is higher than I would like to see. That indicates missing indexes or badly written SQL (or both) and will be contributing to the IO load.
How many disks do you have? Partitions or separate physical devices? How are you database files placed on them?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2008 at 8:52 am
Hi Gail
Thanks for your reply, i appreciate it, will get back to this thread tomorrow when the office is open.
Yes i saw the full scans are pretty high, does that really effect another db that much though?
Great info, once again i appreciate it (more than you realise)
G
July 8, 2008 at 10:34 am
Graham (7/8/2008)
Hi GailThanks for your reply, i appreciate it, will get back to this thread tomorrow when the office is open.
Yes i saw the full scans are pretty high, does that really effect another db that much though?
Remember that all DBs in one SQL instance share the available buffer pool. A full scan means that the entire table needs to be in memory, potentially throwing other things out. It also increases your IO load.
Also full scans often mean that the query is running inefficiently and running longer than it would otherwise need to, making other queries wait
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2008 at 11:05 pm
Hi Gail
Thanks for your help, it seems that increasing the memory allocation solved most of the problems in one go.
With the offending database/application running AND a test SP that calls a number of others to simulate select load on the other database were having problems with
(this script took 165 seconds to run)
Avg sec/read - 0.19
avg sec/write - 0.09
% idle time - 92%
SQL Buffer manager: Buffer cache hit ratio - 99.8
SQL Buffer manager: lazy writes/sec - 0.0
SQL Buffer manager: checkpoint pages/sec - 0.5
SQL Buffer manager: page life expectency - 5926
SQL Access methods: full scans/sec - 430
I know not perfect, full scans need addressing, but the nature of the test queries would give a high score on this.
Thanks for the help, that made a huge difference not only to the other database but also to the offending database/application.
G
July 9, 2008 at 9:08 am
Note that page life expectancy went from low double digits to almost 6000. Isn't it simply amazing that the fact that RAM access is about 4 orders of magnitude faster than disk access can clear up a lot of performance problems!! 🙂
BTW, don't feel bad. You certainly aren't the first person (nor will you be the last) to buy a lot of RAM and have it sit unused by a 32 bit sql server installation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 9, 2008 at 11:42 pm
Looks a lot better, but i think you still have a borderline IO bottleneck. What's your disk layout? (phycisal drives, RAID, file locations)
p.s. What are your memory setting now? (boot.ini switches, SQL settings?) Just for a quick sanity check.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2008 at 11:57 pm
Hi
No, i wont feel bad, i'll just give our DBA some grief, will make me feel much better 🙂
We have Raid 5
C, D, E logical volumes
Sorry not sure about the physical, Raid is beyond me.
TembDB is on C
D holds the Data
E holds the logs
But as i say, not sure how RAID 5 works, but all logical drives are reported to be on one disk in disk management.
Thanks
Graham
July 10, 2008 at 12:08 am
Ouch. Not a good layout.
The thing is, C, D, E are logical splits. They're the same physical devices and it means that IOs occuring on the swap file will impact your database. IOs on TempDB will impact your database. IOs on your database will impact TempDB, etc.
Here's an ideal drive layout. You probably won't be able to implement it because of costs, but please consider it.
RAID 1 array - C drive. The OS, the SQL binaries and the swap file. Maybe the master, model and msdb databases
A Raid 5 (or ideally 10) array - TempDB only
A Raid 5 (or ideally 10) array - Your data files
A Raid 5 (or ideally 10) array - The transaction logs
See also this thread for a similar discussion around disk bottlenecks and resolution
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply