Monthly Archives: January 2016

I am Only Here to Help – sys.xp_logininfo and sys.helplogins

Posted by on January 21, 2016 Comments Off on I am Only Here to Help – sys.xp_logininfo and sys.helplogins

Sometimes you need to find login information. Looking at just the logins on an instance will not allow you to find how a user is connecting. A login maybe nested in an Active Directory group or a login locally to the instance.
SQL server has various commands to provide “HELP”. They can be used to look up or find characteristics of all sorts things like information about users, databases, indexing and much more. This post will look at sys.xp_logininfo and sys.helplogins. SQL 2014 and SQL 2016 have new procedure sys.sp_helpntgroup to further examine rights.

securitylookup

A good practice is to create specific AD groups and add and remove logins from those groups. You standardize the login’s security and access. It also makes it easier to remove access.
Read more »

SQL Agent Jobs Last Run

Posted by on January 20, 2016 Comments Off on SQL Agent Jobs Last Run

Sometimes there is a need to see last run time information for a SQL Agent job. You may need to see when the job is scheduled to run next.  The msdb system database contains a ton of information about SQL Agent jobs schedules, activity, job steps and etc..

I created the below query to get a bird’s eye view of job executions. The query is based off msdb.dbo.sysjobs and msdb.dbo.sysjobactivity. These objects have a lot more information that can be added. Tweak the results to meet your needs.

Jobrollup3

Read more »

Database Statistics Health and Update Scripts

Posted by on January 14, 2016 Comments Off on Database Statistics Health and Update Scripts

I like using Ola Hallengren’s maintenance scripts. Super configuratible, logging, SQl Agent job creation and etc. Sometimes I want to spot check on some of the statistics. Stale statistics can cause cardinality issues with SQL plans.

We learn how database statistics are used:
“The query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results. This topic discusses statistics concepts and provides guidelines for using query optimization statistics effectively.”

The sample rate is set to full s or 100 percent of the rows will be scanned. A full scan will create overhead.  Need to always look at the impact to your environment. You could have old statistics and not used or duplicate statistics.  In some cases, the higher sample rate can actually cause the plan be less optimal.

Rule of thumb, your queries will perform better with fresh statistics.

I have two scripts to I use to find the health of statistics and update statistics based upon certain criteria.
Read more »

Find Object Execution

Posted by on January 9, 2016 Comments Off on Find Object Execution

I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods.

Stored Procedure Executions

Stored Procedure Executions

For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.
Read more »

Identify Lead Blocker Script

Posted by on January 9, 2016 Comments Off on Identify Lead Blocker Script

Many third-party tools have  pretty lead blocking graphs and etc. You may not have access to something than grand.  My friend and co-work JD Pellerin wrote this script. He used this as a basis for an SSRS report that can be executed by operations teams to identify potential batch issues. Play around with tweak it to fit your needs.

Blocking Results

Sample Blocking Results

Read more »

I am Only Here to Help – OBJECT_DEFINITION()

Posted by on January 5, 2016 Comments Off on I am Only Here to Help – OBJECT_DEFINITION()

Many people are familiar the Windows Command Prompt. You can open one. type “HELP” and click enter. It will return descriptions of various OS commands.  You can  type “-?” after a command and get a list on all the switches that can be used with that command. Go ahead and try it (sqlcmd  -?)The system is simply returning metadata.SQL has several commands that return metadata about system and user objects. We will look at the OBJECT_DEFINITION() command.

Windows Command Prompt and HELP command.

Windows Command Prompt and HELP command.

From the link we learn that it “Returns the Transact-SQL source text of the definition of a specified object.” We all know you can right click on an object in SSMS and script it out. Why use this? Output to SSMS has a limit on how much can be returned at a time. Large object like procedures can be thousands of lines long and will be truncated.  AH!

Beside of the nerd cool factor of being able to return source code, it can be handy.  One use is to search for the occurrences of specific objects or syntax within objects. Maybe you want to see what objects reference a certain table or maybe find objects that use a specific QUERY HINT.

OBJECT_DEFINITION applies to the following object types:

  • C = Check constraint
  • D = Default (constraint or stand-alone)
  • P = SQL stored procedure
  • FN = SQL scalar function
  • R = Rule
  • RF = Replication filter procedure
  • TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)
  • IF = SQL inline table-valued function
  • TF = SQL table-valued function
  • V = View

Read more »