Performance Check

Question

Performance Check

Admingod

SSCertifiable

Points: 5997
More actions
June 28, 2016 at 4:55 pm

#327490

Hi,
How to check the performance on the SQL Server for which we recently moved the sql server database to new hardware. How can the comparison be done against old hardware vs new hardware? Thanks for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You have to capture performance metrics from the old system and the new system and compare them. You'd want to look at the wait statistics, percentage of cpu, i/o and memory queues, and general query performance. Capturing these can be done through a combination of sources. System metrics will come from gathering metrics through Performance Monitor. Query metrics can be, less accurately, captured through querying the cache through the dynamic management views (sys.dm_exec_query_stats) or, accurately, by setting up a trace to capture all executions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Admingod SSCertifiable Points: 5997 More actions · Answer 2

Thanks for replying.

Actually we Just moved to new hardware i.e NEW SAN. Server configuration is same. Just wondering after moving to New SAN has made any improvements on the server performance? Any advise?

Thanks

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

I'd say the same thing. Knowing what the wait statistics were before you moved the SAN and after will tell you a lot. Same thing with query metrics. If you never captured any kind of metrics prior to the move, then there is "before" to compare the "after" to. There really won't be any way to see what happened with the new SAN.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Admingod SSCertifiable Points: 5997 More actions · Answer 4

I have monitoring tool which is monitoring the server. However, how can i really measure past vs current. There are chances that lot of activity was going before and less activity now. I am wondering how can i really measure? Is that possible?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

You basically have to pick a representative time from before and a representative time from after. Could there be outliers? Sure. But presumably, unless you've done lots of other changes as well, the primary variable will be the change in disk storage. Focusing on wait statistics and query execution times should tell you what you need.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning