SQL Server Health Check Up

Introduction

We all want highly effective codes which run securely and fast. However, many a times, we build code that runs faster and doesn’t stop to check the environment. The environment we develop our code in, and the tools we use are of paramount importance, and a large dependency of the code is on the database. Hence, in the same light, today I would like to talk about the health checks for SQL Server Database.

Why Do I Need to Know About my SQL Server’s Health?

Like I mentioned before, this tip is important to you since it might slow down your code, or, in some scenarios, completely jeopardize the performance. Hence, it’s always best to be safe, than sorry.

Also, like the engine of your car and your liver, SQL Server needs its own health checkups and tuning for a finer performance. So, here we go.

What Do I Need to Know?

Mostly when we talk about health checkups, there is a common misconception that we need expensive or highly unreliable freeware to check and tune the DB, or, that there are numerable complicated queries which would make your head spin for the same.

It is not that difficult, with this tip, I plan to give a basic knowledge on how we can know what we need to know about our SQL Server’s health and what to do with the information.

We generally use the following to get information about the instances/objects/database connected in a particular session. All these will be taken up in detail later on, however, a brief introduction of what each of the following options has to offer.

• DBCC (Database Consistency Check) Command: talks about various checks that provide the consistency and maintenance of the DB.
• DMV(Dynamic Management Views): These return Server state information. They tell you about objects/instances running, and/or other data which is related to implementation.
• SQL Trace/Profiler – As any other profiler/trace, SQL traces provide us the running capability of a particular database. It gives us analysis of the same queries in different environments up for comparison and helps us tune our queries better. However, trace has been deprecated since SQL Server 2012.
• RML utilities – One of the freeware services provided by Microsoft, we won’t be talking about it much here, but will touch upon how the tool can help.

DBCC (Database Consistency Check) Command

As I have mentioned already, there are some commands which come pre-defined in SQL Database to check the consistency of different objects and the database itself. There are a few options to run, which are common to all these commands.

These options are as under:

• The REPAIR_FAST option performs minor repairs that do not consume a lot of time and would not result in a data loss, e.g., repairing extra keys in non-clustered indexes.
• The REPAIR_REBUILD option performs comprehensive error checking and correction that requires more time to complete but does not result in data loss, e.g. rebuilding indexes
• The REPAIR_ALLOW_DATA_LOSS option performs all the actions of REPAIR_REBUILD and adds new tasks that may result in data loss, e.g., page errors
• ALL_ERRORMSGS is used to display unlimited number of errors.
• NO_INFOMSGS suppresses all informational messages

The various commands that are used along with their syntax are as under:

DBCC CHECKDB (‘Database_name’ [, NOINDEX| {REPAIR_ALLOW_DATA_LOSS| REPAIR_FAST| REPAIR_REBUILD}]) _
[WITH {[ALL_ERRORMSGS][, [NO_INFOMSGS]][, [TABLOCK]][, [ESTIMATEONLY]][, [PHYSICAL_ONLY]]}]

To check the overall consistency of the database.

DBCC CHECKTABLE(‘table_name’ | ‘view_name’ [, NOINDEX|index_id| _
{REPAIR_ALLOW_DATA_LOSS| REPAIR_FAST| REPAIR_REBUILD}]) _
[WITH {[ALL_ERRORMSGS | NO_INFOMSGS][, [TABLOCK]][, [ESTIMATEONLY]]

Similar command to check tables, here in this case, after we run CheckDB, we might get a few tables as the problem, we can run this command with repair option and solve the problem.

DBCC CHECKALLOC (‘database_name’[, NOINDEX| {REPAIR_ALLOW_DATA_LOSS
|REPAIR_FAST|REPAIR_REBUILD}])[WITH {[ALL_ERRORMSGS | NO_INFOMSGS][, [TABLOCK]][, [ESTIMATEONLY]]}]

There are times when we have to check the database pages, as they might be the root cause of a problem.

"DBCC CHECKCATALOG (‘database_name’) [WITH NO_INFOMSGS]

This DBCC command checks the consistency of database’s systems tables, which we will consider in a short while. System tables are very important and should not be ignored.

DBCC CLEANTABLE({‘database_name’|database_id}
, {‘table_name’|table_id|’view_name’|view_id}[, batch_size])

This DBCC command reclaims space. Generally, when we use ALTER TABLE to drop a column (variable size or text), we forget about the space it occupies in the database memory. This command reclaims all these spaces in one go, opening up fresh space for more data.

DBCC INDEXDEFRAG({database_name|database_id|0}
, {table_name|table_id|view_name|view_id}, {index_name|index_id}
) [WITH NO_INFOMSGS]

This DBCC command defragments the index pages, and compacts the index. It is possible to use DBREINDEX; however, DBCC INDEXDEFRAG is less intrusive than DBREINDEX, but takes longer than rebuilding a highly fragmented index. This can be run online and is transaction-oriented.

DBCC DBREINDEX([‘database.owner.table_name’[, index_name [, fillfactor] ]
 ]) [WITH NO_INFOMSGS]

This DBCC command is used to rebuild one or more indexes on a database. Use this command only when DBCC INDEXDEFRAG does not resolve the fragmentation.

To speed up re-indexing, defrag SQL server database and log files. To perform this task, the mssqlserver and the sqlserveragent (and any other related) services must be turned off. Therefore, defragging the SQL server files should be done whenever there is a scheduled down time.

DMV (Dynamic Management Views)

As already mentioned, DMV commands give us reports on the exposed metadata that is used during implementation.

• There are two kind of DMVs, Server scoped and DB scoped DMVs. There is not much difference in the functionality barring the permission required for the two. For Server scoped, we need VIEW SERVER STATE and we need VIEW DATABASE STATE for the others.
• The syntax to be followed generally is ^sys.dm_
• Now there is a large category in which these DMVs are divided and each tells you more about the running instance. Some of these categories are AlwaysOn Availability Group Dynamic Management Views and Functions (these tell you more about cluster nodes, cluster members, page repairs, etc.), I/O Related Dynamic Management Views and Functions (as the name suggests, it is related to give all the information regarding the I/O systems) and Change Data Capture Related Dynamic Management Views (majorly talks of transitional information, error logs, etc.)

  You can read all of these in the link given below:

  • https://msdn.microsoft.com/en-us/library/ms188754.aspx

  An example for the same would be:

  SQL

  SELECT * FROM sys.dm_cryptographic_provider_algorithms(provider_id);

  It returns the algorithms provided by a Provider key which is given as an input by the user. This provider Id is Identification number of the EKM (Extensible Key Management) provider.

• In order to find all the DMVs and DMFs, we can use the following query run in an instance of SQL Server.
  SQL

  Select Name,type, type_desc From Sys.system_objects where name like ‘dm_%’ order by name

• DMVs can be very effective especially in terms of identifying unused Indexes and managing that space.

  It is a problem if there is a lack of Indexes since that can result in too many table scans and too many indexes can result in too much overload during data update.

Effective Index usage can be applied here by using the DMV as under,

Sys.dm_db_index_usage_stats which keeps track of all the indexes being used. There is a lot more DMVs can do with regard to this, however, that could be the topic of another post as it is too long.

SQL Trace/Profiler

It is a rich interface to create and manage traces which help us get results and analyze them in order to perform better.

The events that are saved in a trace file can be later analyzed or used, and replay a specific series of steps taken while troubleshooting.

SQL Server profiler displays results based on pre-defined properties. They can be copied to use in SQL Server or DB Engine Tuning advisor for better performance.

Even though SQL Trace is deprecated as of SQL Server 2012, some people still haven’t upgraded yet, so until then.

RML Utilities

Replay Markup Language utilities are freeware services provided by Microsoft for the maintenance and tuning of SQL Server. This tool helps you gauge the usage of the server.

Some really important information like what view/procedure/query is using maximum resource, for that matter, which login is using the maximum resources can also be told using this freeware.

Performance management gets easier as it compares various workloads and reproduces the same in different environments, e.g.: this tool can help us figure out which query is executing slower than usual.

There are various other such advantages that can associate with such freeware… for more information, you can visit the following links:

• https://support.microsoft.com/en-us/kb/944837
• http://www.sqlpassion.at/archive/2011/06/21/replaying-workloads-with-the-rml-utilities/

Though this barely scratched the topic of SQL Health check up, hope it was indeed helpful.

Happy coding!

References

• https://support.microsoft.com/
• https://msdn.microsoft.com/