SQL Server Performance

Question

Post reply

SQL Server Performance

hkflight

SSCommitted

Points: 1949
More actions
January 25, 2012 at 12:04 pm

#254233

I am completely new to analyzing SQL Server performance. I tried PerfMon and walked through an article on MSDN, but the article was written for an older version of PerfMon and it didn't translate well. I am trying SQL Check from Idera, and produced the following graph (attached). We have been experienceing slow performance and I am trying to get to the bottom of it. Hopefully someone can shed some light or point me in the right direction. Thank you very much for your help.
I am running SQL Server 2005 Enterprise x64. The server is running on Windows Datacenter 2008 R2 x64 in VMWare 4. It has 48GB ram allocated to it (40 for sql server) and 4 vCPU's. It is the only VM running on the physical box which is a dual Intel 6-core Xeon with 64GB total RAM.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

And maybe also http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hkflight SSCommitted Points: 1949 More actions · Answer 2

These articles look great for looking at specific queries, but my trouble is with the entire DB running slow. Simple operations that were not slow a couple months ago are now slow. I am thinking it has to do with our SAN or Server configuration. We are considering rebuilding the VM and using 8 vCPU's and upgrading to SQL Server 2008 R2. I simply do not know what are good value for the different metrics in the attached image given the server specs I provided.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

is your database growing over time? Are the indexes and statistics being maintained? This would be the first thing to check.

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

hkflight (1/25/2012)
These articles look great for looking at specific queries, but my trouble is with the entire DB running slow.

Those articles are for when the entire database is slow, they're written to show you how to find the main causes of the slow performance (it's usually just a few procedures/queries causing all the problems) and give you a very rough idea how to approach fixing 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hkflight SSCommitted Points: 1949 More actions · Answer 5

Autogrowth is set to %10 and it has only grown once in the past 9 months. Indexes are rebuilt quartely and are maintained by the software vendor who created the database. I have not taken any steps to regularly rebuild statistics, but I believe they are updated when the indexes are rebuilt.

Thank you for your help!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

hkflight (1/25/2012)
Indexes are rebuilt quartely and are maintained by the software vendor who created the database.

Quarterly? As in every 3 months?

Weekly checks for fragmentation and rebuild are probably more normal. Yes, stats are updated when the indexes are rebuild (and automatically if auto_update is on), but there are cases where stats go stale in a day or two, extreme cases an hour or two.

I suspect that maintenance needs to be done a lot more often. It's probably not the sole cause of performance problems, I'm willing to bet that's data growth combined with non-optimal code and inadequate indexes, but it will certainly contribute.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hkflight SSCommitted Points: 1949 More actions · Answer 7

One thing I notice is that no matter how much RAM I allocate to this vMachine, SQL Server utilizes all of it. Even when the server is under no load, SQL uses 40GB of RAM.

Is this an indicator of a problem?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Normal behaviour. SQL will generally use memory up to the amount on the server or max server memory (which by default is 2 TB).

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 9

agree with Gail, no way rebuilding indexes quarterly is frequent enough, especially as you are seeing a slow down over time.

Whilst you investigate your slowest queries and dig deeper into the indexing strategy I would get an index rebuild scheduled asap.

As the database is growing slowly you may not be getting large enough changes in data for auto update stats to kick in, so it would not surprise me if a quick exec sp_updatestats gives performance improvements.

---------------------------------------------------------------------

hkflight SSCommitted Points: 1949 More actions · Answer 10

Thank you both very much for your help. Unfortunately, I cannot control indexes or queries as they are part of the software and if I changed them, future updates to the software would break things.

They recommended rebuilding the indexes quarterly, but I can certainly do it weekly. I can also rebuild stats nightly if that will help performance since we are a 7-6 operation. Thank you both again for your help.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Gosh... I check for "out of spec" indexes every night and either reorg or rebuild depending on how bad they may have gotten.

Shifting gears a bit... we had a similar problem where everything on the server slowed down to a comparative crawl. It, too, was on a dedicated VM box. The culprit turned out to be a bad memory chip. Not sure how they found the bad chip but when they replaced it, everything went back to screeming speeds.

Or... it could just be (as Gail suggested) some code having a real problem because the scale of the tables increased. Of course, there could also be other factors but it's one of the first things that I'd check. Gails articles are a perfect starting point for that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

hkflight (1/25/2012)
Thank you both very much for your help. Unfortunately, I cannot control indexes or queries as they are part of the software and if I changed them, future updates to the software would break things.

Then you need to get the vendors involved. Work through my articles, find the worst performing queries and take it up with the vendors and ask that they fix it or give you permission to fix it. Managerial support help here.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

If you are a DBA, what is stopping you from running index rebuild / defragmenting it? IIRC vendors or third party licensing agreements stop us from design changes (add tables, partitioning etc.) but maintenance is generally not covered in it.

hkflight SSCommitted Points: 1949 More actions · Answer 14

I can and will rebuild more frequently than they prescribed. . Thank you all for your help!