Dynamic Management Views and Objects

Read more about the article sys.dm_exec_cached_plans
Microsoft SQL Server


The dynamic management view sys.dm_exec_cached_plans returns information on query plans currently in memory. This information can be useful for a lot of reasons: checking total memory allowed for cached plans, which plans aren’t getting reused, searching for certain query plan operators (for instance, scans), which query plans are high memory consumers etc.


Continue Readingsys.dm_exec_cached_plans
Read more about the article Capturing Long Running Queries
Microsoft SQL Server

Capturing Long Running Queries

Long running queries in a database may or may not be a problem, depending on the type of system. In a data warehouse solution, queries that runs for several minutes may be perfectly normal and nothing to worry about. But in an OLTP system, long running queries may cause performance problems. Read on to see how to monitor for long running queries in SQL Server.


Continue ReadingCapturing Long Running Queries
Read more about the article Mass editing of SQL Server objects
Microsoft SQL Server

Mass editing of SQL Server objects

Ever had the need to replace a piece of code in multiple SQL Server objects (Stored Procedure, Trigger, Function or Views)? Here’s a method to search for a string in all objects, replace it with some other code, and to finally script all changes. In other words, a method for mass editing SQL Server objects.


Continue ReadingMass editing of SQL Server objects
Read more about the article Logging changes to sys.configurations
Microsoft SQL Server

Logging changes to sys.configurations

Some server wide settings can have major negative impact if they are changed in your SQL Server environment. So as a DBA it is a good practice to monitor changes to these setting, and to raise alerts when they are changed. To do this, the system view sys.configurations can be monitored for changes.

This blog post will demonstrate a way to monitor SQL Server configuration settings. The solution will also give you get the complete history for the different settings in sys.configurations.

Note that database mail needs to be configured for the current solution to work properly.


Continue ReadingLogging changes to sys.configurations
Read more about the article List SQL server roles by users
Microsoft SQL Server

List SQL server roles by users

SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.

Here I’ll show a SQL script that lists all users and their server roles.


Continue ReadingList SQL server roles by users
Read more about the article A replacement for syslogins
Microsoft SQL Server

A replacement for syslogins

The system table sys.syslogins was marked as deprecated in SQL Server 2005, and is included up until 2012 for backwards compability only.

So what is the replacement of sys.syslogins? As it turns out, there is no exact match in SQL Server 2005 and later. Instead there are various system views that can be combined to gather the same information.

I created a SQL statement to combine those system views into a single view, matching the old sys.syslogins.


Continue ReadingA replacement for syslogins