Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a SQL Server 2005 instance that is experiencing some slow times. This link shows the perfmon details. Since I don't analyze this type of data daily, I was wondering if there a some real problems with the I/O system.

The server is using a SAN and the DB files and Tempdb files are on the same drive(E:). Not the best architecture, but I don't have control over the server. The server is used to run reports using Cognos, so it is mostly a read only DB.

Thanks

Here is some fun code that needs to be corrected.

select "tempSalesRpt_SubRgnDist"."Region" AS "Region",
   min("tempSalesRpt_SubRgnDist"."RegionName") AS "Region_Text"
from "SalesReporting"."dbo"."tempSalesRpt_SubRgnDist" "tempSalesRpt_SubRgnDist",
        (select "SecurityMaster"."Userid" AS "Userid", "SecurityMaster"."SoldTo" AS "SoldTo"
        from "SalesReporting"."dbo"."SecurityMaster" "SecurityMaster" 
        where "SecurityMaster"."Userid" = lower ('USTGACA')) "SecurityMaster4" 
where NOT "SecurityMaster4"."Userid" is null 
    and "tempSalesRpt_SubRgnDist"."SoldTo" ="SecurityMaster4"."SoldTo" 
    group by "tempSalesRpt_SubRgnDist"."Region" 
    order by 1 asc , 2 asc

The securitymaster table is accessed on every query and that is the table that has increased recently.

I would assume that this code has non sargable code, but the execution plan shows that index seeks are used and key lookups.

I do see some new indexes could help but need to dig further.

share|improve this question
    
The stats you posted don't seem to have any data for Page I/O latch waits. Could you do another run and get page I/O Latch waits? Also, you might want to look at technet.microsoft.com/en-us/library/cc966540.aspx for a basic rundown on performance troubleshooting. –  ConcernedOfTunbridgeWells Jul 10 '09 at 14:22
add comment

2 Answers

up vote 1 down vote accepted

Based on the data provided you've got something going on. Your disk queues are seconds per read and seconds per write counters are a lot higher then you want them. Now the catch here is that this doesn't mean its a disk problem, just that the disks are getting slammed. You could have an indexing problem, or a statistics problem, which is causing the SQL Server to hit the disks harder than it needs to.

Start by looking at the indexes in the database, and see if any new indexes need to be created. This will increase the size of your database but you'll see a reduction of traffic to the disk and a decrease in query run time.

You can start by looking at the execution plans of your long running queries which will tell you where you need to add indexes.

share|improve this answer
    
Looks like I have some queries with non sargable syntax. I believe the data increased recently, which would explain for slower performance. –  Dwight T Jul 10 '09 at 20:07
    
If you've got key lookups happening that's bad. Those are going to kill your performance. Whatever column is being returned by the key lookup add that as an included column to the index being seeked. –  mrdenny Jul 12 '09 at 18:29
add comment

In addition to Denny's answer you have about 5GB free memory on your box - have you configured SQL's memory correctly?

If you have a 32 bit system you can address more memory by enabling AWE :

http://technet.microsoft.com/en-us/library/ms190673(SQL.90).aspx

SQL will use this extra memory to cache more database tables/indexes (assuming your database is bigger than your free memory).

share|improve this answer
    
The database is about 1.5Gig. –  Dwight T Jul 10 '09 at 19:50
    
How much memory is installed on the box? Is it 32bit or 64 bit? What OS and edition is installed? Is there just one 1.5GB user database on the box? Do you have AWE configured or the /3gb switch set in the boot.ini? Is the box dedicated for SQL? Are you rebooting the box regularly? –  SuperCoolMoss Jul 10 '09 at 21:40
    
Why would you want to reboot the box regularly? –  mrdenny Jul 12 '09 at 18:30
    
Hello Denny. I posed the question in case they were rebooting the box - not that I'd recommend it. It's strange that a 1.5gb db that should be cached in memory is thrashing the drives. –  SuperCoolMoss Jul 13 '09 at 11:36
1  
Yes if you're running on a 32 bit platform then you have to configure AWE to allow SQL2005 to address memory above 4GBs. –  SuperCoolMoss Jul 21 '09 at 11:54
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.