July 15, 2015 at 5:24 am
Hi everyone. I am quite new to this so please be kind! 🙂
I have been given a project to centralise all our databases onto one DB server. Currently in the phase of testing the concept however I am concerned whether or not the server will cope with the load we will put on it. I have done much research and I am currently looking at the following stats:
\Memory\Pages/sec
\Network Interface(*)\Bytes Total/sec
\PhysicalDisk(_Total)\% Disk Time
\PhysicalDisk(_Total)\Current Disk Queue Length
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:General Statistics\User Connections
\SQLServer:General Statistics\Transactions
\SQLServer:General Statistics\Active Temp Tables
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Lock Blocks
\SQLServer:Memory Manager\Total Server Memory (KB)
\System\Processor Queue Length
\Processor(_Total)\% Processor Time
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:General Statistics\Processes blocked
\SQLServer:Locks(_Total)\Lock Waits/sec
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\PhysicalDisk(_Total)\Avg. Disk Queue Length
Is there anything else I need to be looking at which is not in the list above?
Thanks
July 15, 2015 at 5:43 am
print_mug (7/15/2015)
Hi everyone. I am quite new to this so please be kind! 🙂I have been given a project to centralise all our databases onto one DB server. Currently in the phase of testing the concept however I am concerned whether or not the server will cope with the load we will put on it. I have done much research and I am currently looking at the following stats:
\Memory\Pages/sec
\Network Interface(*)\Bytes Total/sec
\PhysicalDisk(_Total)\% Disk Time
\PhysicalDisk(_Total)\Current Disk Queue Length
\SQLServer:Access Methods\Page Splits/sec\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:General Statistics\User Connections
\SQLServer:General Statistics\Transactions
\SQLServer:General Statistics\Active Temp Tables
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Lock Blocks
\SQLServer:Memory Manager\Total Server Memory (KB)
\System\Processor Queue Length\Processor(_Total)\% Processor Time
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:General Statistics\Processes blocked
\SQLServer:Locks(_Total)\Lock Waits/sec
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\PhysicalDisk(_Total)\Avg. Disk Queue LengthIs there anything else I need to be looking at which is not in the list above?
Thanks
I've crossed out the useless ones.
You have nothing monitoring overall memory usage (and pages/sec may not be what you think it is. SQL Server doesn't use the page file), you have nothing looking at disk latency, you're not looking at waits, you're not looking at the SQL schedulers (hint, SQL doesn't tell Windows to queue up tasks for the processors)
It's a good start. Check Jonathan's old blog, he did a lot of consolidation and should have several blog posts on the subject http://sqlblog.com/blogs/jonathan_kehayias/, also his newer blog https://www.sqlskills.com/blogs/jonathan/
This may also be useful, especially the first chapter http://www.red-gate.com/community/books/accidental-dba
Ultimately, you're going to have to do this bit by bit. Get performance baselines for all the current servers, identify similar workloads (OLTP vs reporting, etc), move one database, monitor, rinse, repeat until you've either done all databases or (more likely) pushed the new environment as far as is safe. Don't forget to take things like month end processes into account.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply