How determine multiple DB usage/workload?
-
Could use any suggestions or info regarding best way to determine how much (or often) a SQL database is used (it's workload, transaction usage, etc.)
I have been tasked with moving 20+ SQL 2000 databases that currently reside across 5 or 6 servers, onto a new "clustered" SQL server. Boss wants to "load balance" (?) these databases on the new clustered server. I am new to the clustered SQL server arrangement, but have successfully moved SQL database between servers in the past.
Any advice regarding Clustered SQL server would also be appreciated....
Dave Gradoville, MCDBA -
Brian Knight has some articles on clustering on this site. As far as load balancing, I'm assuming you're active/active and you are looking to move some dbs to each server to balance.
As far as workload, it's a bunch of stuff. You can get the box load, CPU, Memory paging, disk, etc. and figure out some "balance" between the systems. You might want to look at the times of peaks as well to be sure you are not putting a bunch of dbs on one server that peak at the same time, like 4:30.
On the SQL Server, the biggest thing I'd watch for is tempdb usage. You'd like to balance the tempdb load if you are consolidating servers since tempdb is shared.
-
I think SQL Profiler should be a good place to start. You can just start a standard trace and direct the output to a table. Make sure that you have the databaseid column selected in the trace. You can execute queries against your output table checking for reads, writes, duration and group them by the database to give you some output. Also, look for no. of connections against each database.
-
SA24/Steve
Thnaks for the reply and advice. I have used SQL profiler in the past, but not to a table. I will attempt it - and include the columns Steve mentioned (CPU, memory, disk, etc.).
Dave G.
Dave Gradoville, MCDBA -
Windows Performance Monitor will help you out with tracking the stress of the hardware / os / sql server. There's some great articles on this site that cover performance monitoring.
As far as determining the typical workload of the server, use SQL profiler to trace the SQL that is being executed on your box. Logging the trace to a file will let you run other tools like Index Wizard (beware it can give bad suggestions) and you can keep the file for future reference, to see how your workload changes.
Julian Kuiters
juliankuiters.id.au -
We're just exploring clustering ourselves.
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/scconbp.mspx
The big thing is that there is no real load balancing for SQL Server in a clustered environment. If you are doing N-Node failover or Failover Ring any one server can get overloaded.
The easy way to explain it is the you have SQLSrvA, SQLSrvB, SQLSrvC.... (up to 8 nodes in a W2K3 enterprise cluster). They have a front-end virtual server that is saying I'm SQLSrv_Primary that tasks the connections out to the individual servers. Unless you are breaking them up into multiple clusters?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply