Interesting SQL Server Metric - Installation Date

Hello,

Do you collect metrics about your SQL Server environment? Wait, you don't? Here are some metrics you should collect.

• How many SQL Servers are installed?
• How many SQL Servers are installed?
• How many SQL Servers are used as production
• How many SQL Servers are used as development?
• How many SQL Servers are used as QA and UAT?
• How many SQL Servers are used as disaster recovery?
• How many SQL Servers are at your location, cloud, other sites, etc.?
• What applications are using each SQL Server(s)?
• How many database do you have?
• How big are the databases and total size of disk use?

Why collect metrics? Well there are many reasons, some are to help manage the SQL Servers and other are to show management what is happening. With management in mind, you should collect the date that SQL Server was installed. With installation date you can show many interesting facts to management.

Here is a list of just what you can show to management.

- How many SQL Servers you have
- How many are where installed each month, quarter and/or year
- How many SQL servers need to be upgrade (if you also collect versions/patch levels)
- What is the growth rate over any period

You can start to see that there is metrics here that management would love to see.

Below is a script that will show the server name, version and installation date.

SELECT @@SERVERNAME as 'SQL Server Name'
      , @@VERSION as 'SQL Server Version'
      , createdate as 'Date Installed' FROM sys.syslogins
WHERE [name] = 'NT AUTHORITY\SYSTEM' AND [dbname] = 'master';

Hope you find this script useful and have a happy holiday and a Happy New Year.

Rudy

SQLCAT.com - eBooks for Free

Hello,

Today I found out that SQLCAT has some free eBooks that are great and I highly recommend downloading them.

eBooks available are:

SQLCAT's Guide to BI and Analytics
SQLCAT's Guide to High Availability and Disaster Recovery
SQLCAT's Guide to Relational Engine

Lots of good information and worth the download.

Here is the link:
http://blogs.msdn.com/b/sqlcat/archive/2013/10/23/sqlcat-com-ebook-downloads.aspx

Thanks and enjoy,

Rudy

Script Performance Increase with CURSOR Parameters

Recently I attended the PASS Summit 2013 and found an interesting performance tip with the CURSOR statement. Cursors are not always the best choice but when you have to use one; check out the parameters that can be used.

Below is part of my original cursor that I used in one of my script. The total script executed in 6 seconds

…
DECLARE myInstances CURSOR FOR
SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
….

Making the following changes by adding LOCAL FAST_FORWARD after the CURSOR statement.

 …

DECLARE myInstances CURSOR LOCAL FAST_FORWARD FOR
SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable
..

By adding this change, the same script executed in 3 seconds. That’s a 50% improvement in performance.

Books On Line and the following links provide more details as on this and additional parameters you can use with the CURSOR statement. 

Increase your SQL Server performance by replacing cursors with set operations

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx

Understanding SQL Server Fast_Forward Server Cursors
http://blogs.msdn.com/b/sqlqueryprocessing/archive/2009/08/12/understanding-sql-server-fast-forward-server-cursors.aspx

Comparing cursor vs. WHILE loop performance in SQL Server

http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741

Curious cursor optimization options

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
 
Have a look at the above links and try some testing of your own. You too will be surprised at the performance increase you can get. My own testing showed that I can get from 10% - 70% performance increase with simple changes.

Ensure you test these parameters fully before applying to your production server.

Thanks,

Rudy

 

SQL Server Diagnostic Information Queries

Good Day!

Since I didn't have this great link before, I would like to share it with you now. If you need to perform some diagnostics on your SQL Server, Glenn Berry has created an excellent SQL scripts that will help you out. There are versions for each version of SQL Server.

Here is the link: http://sqlserverperformance.wordpress.com/?s=Diagnostic+Information+Queries

I highly recommend you try them out now before you actually need them. Check the site regularly as he updates the scripts as needed.

Enjoy!

Rudy

Who is Active on the SQL Server

Hello everyone,

It's been a while but I'm back.

Today I would like to share a link to Adam Machanic's blog page that has a very cool SQL script.

The script is called sp_WhoIsActive and what it shows is everything you wanted to know about who is doing what on your SQL Server. I highly recommend it and you should try it for yourself, on a development server. 

Look at the link here: http://sqlblog.com/files/folders/release/tags/who+is+active/default.aspx

Enjoy!

Rudy

Hidden Features of SQL Server

Hello Again,

Here is an interesting web page that shows hidden features of SQL Server which are not documented. There are stored procedures and other tricks which are very useful.

http://stackoverflow.com/questions/121243/hidden-features-of-sql-server

Below is a small list from the site:

Stored Procedures

• sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
• sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
• sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
• sp_helptext: If you want the code of a stored procedure, view & UDF
• sp_tables: return a list of all tables and views of database in scope.
• sp_stored_procedures: return a list of all stored procedures
• xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
• xp_fixeddrives:: Find the fixed drive with largest free space
• sp_help: If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1

Snippets

• Returning rows in random order
• All database User Objects by Last Modified Date
• Return Date Only
• Find records which date falls somewhere inside the current week.
• Find records which date occurred last week.
• Returns the date for the beginning of the current week.
• Returns the date for the beginning of last week.
• See the text of a procedure that has been deployed to a server
• Drop all connections to the database
• Table Checksum
• Row Checksum
• Drop all the procedures in a database
• Re-map the login Ids correctly after restore
• Call Stored Procedures from an INSERT statement
• Find Procedures By Keyword
• Drop all the procedures in a database
• Query the transaction log for a database programmatically.

Functions

• HashBytes()
• EncryptByKey
• PIVOT command

Misc

• Connection String extras
• TableDiff.exe
• Triggers for Logon Events (New in Service Pack 2)
• Boosting performance with persisted-computed-columns (pcc).
• DEFAULT_SCHEMA setting in sys.database_principles
• Forced Parameterization
• Vardecimal Storage Format
• Figuring out the most popular queries in seconds
• Scalable Shared Databases
• Table/Stored Procedure Filter feature in SQL Management Studio
• Trace flags
• Number after a GO repeats the batch
• Security using schemas
• Encryption using built in encryption functions, views and base tables with triggers

 
Have a look and book mark the link for future use, or just come back here :)
 
Enjoy,
 
Rudy

Microsoft Feature Pack and Upgrade advisor for SQL Server 2012 SP1

Hello,

The link below it the Microsoft site for additional feature packs that you can install onto your SQL Server 2012 SP1 installation. There are a hand full of excellent features you can add. One of them is the upgrade advisor.

Here is what Microsoft says about the upgrade advisor

"Microsoft Upgrade Advisor analyzes instances of SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 in preparation for upgrading to SQL Server 2012. Upgrade Advisor identifies feature and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it. "

http://www.microsoft.com/en-us/download/details.aspx?id=35580

Upgrade advisors are also available for older versions of SQL Server, just do a search on the link above.

It's a great tool to help you migrate/upgrade your older SQL Servers to the newer version.

Enjoy,

Rudy

Microsoft Support Lifecycle for SQL Server

Hello Everyone,

Here is a link to find out when your version of SQL Server will be out of support.

http://support.microsoft.com/lifecycle/?c2=1044

You can save the page to a CSV file or just go to the site for updates.

Enjoy!

Rudy

SQL Server Version Numbers and Build List

Hello everyone,

Ever need a quick place to find out the versions/build numbers for any SQL Server?
Try this link: http://sqlserverbuilds.blogspot.ca/ which is a Microsoft blog. The site have all the versions and links to each service pack and cumlative patch you will ever need.

Its very handy!

Rudy

Locate Dangerous SQL Server Setting

Hello everyone,

After view some information regarding dangerous SQL Server settings; I decided to write a script that will check your current SQL Server settings and point out any dangerous configurations. There are many settings that should not be used unless you are directed by Microsoft support, Executing this script will help you determine if any of the danerous settings are present.

If any of the dangerous settings are detected, an explaination is provided as to why it is dangerous to use the setting(s)

Enjoy!

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DdzI2UlF6X3U3Q2M/edit?usp=sharing

SQL 2012 Always On Replication Monitoring

Hello everyone,

A new feature in SQL 2012 is the Always On replication. It will allow you to replicate a database to a another SQL server and make it available in READ ONLY mode. This is a great addition to SQL Server and you can find out more in BOL (Books On Line) and a lot of places on the internet.

Once Always On is configured and running you should monitor its status to ensure that data is replicating. I have created the script below to help perform this task. You can add to this by placing the results in a table and have a SQL job update the table. Once an error is detected, you can have a report or email alert generated. It's basically up to you.

The last section of the code is commented out. This section is for testing the main script and you should modify and try it out then note the output from the main script.

This script creates no tables or stored procedures but you should test it on your development system first.

Enjoy!

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DNy1OcVBhWmdRZFk/edit?usp=sharing