SQL Server – How to Get Useful Database Performance Statistics

performancesql serversql-server-2008-r2

We currently have several shared SQL Server servers and instances in our organization. They are going with a different architecture where business units will get their own SQL Server instances. We have SQL Server 2008R2.

In the past, databases were put on servers in a more or less random fashion — business use and disk space were the main drivers.

I've been tasked with coming up with a plan for allocating our 50+ databases across 3 servers (one instance per server). My criteria for divvying them up is as follows:

Business critical db's should be spread across the servers. i.e. avoid lumping a bunch of business critical db's all one server.
For the remainder of the non-mission critical db's, consider:
a. Disk space usage
b. I/O usage — memory, CPU, etc.

1 and 2a are easy for me to figure out. I know how to get data file sizes.

My question is with that last bit – 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.

Frankly, I'm not 100% sure how to phrase this question! I think I basically want to know is, "How 'busy' is this db — however you define 'busy'?" Or maybe, "How does this db impact the performance of the server?"

I mean, disk space is important — but it could (in theory) have a ton of rows or binary objects in there that are not written very often, so it has little impact on the performance of the server. I think I would want to know how much impact a db within a date range.

Is there a way to get these kind of general stats? I'm not looking for expensive queries, detailed user stats, or query optimization here. I'm aware of the SSMS reports, but they seem to be more real-time. The end goal is to divide up our current databases into evenly balanced buckets, and I would think performance is one way to do that.

Thoughts? Wisdom? Any other ideas on how to divvy up the db's?

Thanks! Tom

Best Answer

My question is with that last bit - 2b. I do not know how relevant this is, what specific stats to look at, or how to get them.

If you are tight with budget for a good third party tool - Performance Advisor (note: We use do not use 3rd party tools - only SQL Server DMVs/XEvents, etc homegrown over the years), you can look at sys.dm_os_buffer_descriptors DMV. Remember that you have to have a job that dumps the data from the dmv to a physical table, since the DMV data get reset.

From my answer :

--Find out the database that consumes highest memory in buffer pool 
SELECT COUNT(*) AS cached_pages_count , 
    ( COUNT(*) * 8.0 ) / 1024 AS MB , 
    CASE database_id 
      WHEN 32767 THEN 'ResourceDb' 
      ELSE DB_NAME(database_id) 
    END AS Database_name 
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id

if you want something like CPU usage, Memory usage, Network usage and Disk space usage overtime then use Performance Data Collector.

How 'busy' is this db -- however you define 'busy'?"

Busy - I would define as

how much space the database uses in buffer pool ?
How much IO it is generating ?
CPU used by databases.
Is the database involved in read (reporting) or more write (OLTP) activity ?

Its very important to do a Baselining of your sql server with SQL Server Dynamic Management Views

Once you have proper data collected, you can easily figure out what databases to group together.

Related Solutions

Sql-server – Sharing certificates encryped by password between DBs and instances

You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:

CREATE CERTIFICATE ERecruitStatsGatheringCert        
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'       
WITH PRIVATE KEY (
    FILE = 'd:\DavesCert.pvk',  
    DECRYPTION BY PASSWORD = 'S3creT!');

But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:

Create a cert in the user DB
Add the signature to the store procedure(s)
Drop the private key of the certificate
Backup/restore that cert to master (public key only!)
Create login in master, assigned that permission, etc.

Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.

Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.

Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].

Sql-server – Required hardware to build a development environment in Sql Server

Ideally, you'd have the same configuration in development as you do in production. That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod. I'm guessing that this would be a pretty significant drop from what you have in production. But given that few places have the budget to have dev match prod, I understand dealing with what you have.

With 10 instances, the part I'd be concerned about is the RAM. Figuring that somewhere around 4-6 GB should be held back for the OS, any other software on that server (A/V, etc.), and an emergency buffer, this means you have about 26-28 GB of RAM for 10 instances, or under 3 per instance. I don't know what you're averaging now, but it's probably a good bit more.

As such, I'd try to max out that server's RAM and set the maximum memory per instance equal to what it is in production. Even though there won't be many people using the server, skimping on RAM will give you false negatives with performance and will make it harder for developers to get accurate performance tests.

Unless you have dozens of developers constantly going at the databases, you'll probably find CPU to be adequate (though not really good). The low disk speed is a bit concerning, but get enough RAM and the disk speed shouldn't be too big of an issue for a development server. Again, you'll likely find additional performance-related problems in dev that you won't in prod (if you have more CPUs per server and faster disks and more spindles available per server), but when you're on a budget, those are the trade-offs you have to make sometimes.

Edit

I would be totally remiss if I didn't point out Glenn Berry's book on the topic.

Best Answer

Related Solutions

Sql-server – Sharing certificates encryped by password between DBs and instances

Sql-server – Required hardware to build a development environment in Sql Server

Related Question