1

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.

Improve this question
1
  • 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. Commented Jul 10, 2009 at 14:22

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • Looks like I have some queries with non sargable syntax. I believe the data increased recently, which would explain for slower performance. Commented Jul 10, 2009 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. Commented Jul 12, 2009 at 18:29
0

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).

Improve this answer
6
  • The database is about 1.5Gig. Commented Jul 10, 2009 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? Commented Jul 10, 2009 at 21:40
  • Why would you want to reboot the box regularly? Commented Jul 12, 2009 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. Commented Jul 13, 2009 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. Commented Jul 21, 2009 at 11:54

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.