Tag Archives: dmv

sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

sys.dm_server_registry – New Dynamic Management View (DMV) in SQL Server 2012

As I said earlier either that SQL Server giving more & more power to the administrator of SQL Server by making SQL Server related information available in SQL Server Management Studio directly. Information is two side bladed swords, if you don’t use it wisely than you will be in trouble.

Note: Registry is very sensitive part and change without actually know what exactly you are doing, you will be in trouble.

So far, if you wanted to get some registry level information for SQL Server, you had, mostly, two options.

1.)    Go to registry from Windows

2.)    Use “xp_RegRead” extended stored procedure

SQL Server 2012 is giving you better way to handle this by providing sys.dm_server_registry. You can simply query this new dynamic management view and find all SQL Server related registry with its name and value right from your SQL Server Management Studio. Isn’t it fantastic?

You can set alert email by querying some important value in SQL Server JOB. For example you would like to get an email whenever port of SQL Server changes.

Let us now query this DMV and see its result:

 SELECT * FROM sys.dm_server_registry 

Here is the output of above query :

You can read my earlier article I written for “XP_RegRead” from here.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Sys.dm_server_services – new DMV in SQL Server 2012

Sys.dm_server_services – new DMV in SQL Server 2012

Microsoft is giving more power to the user of Microsoft SQL Server with each release of SQL Server. SQL Server 2012 is no exception and came up with lots of new features, tools, DMVs, system stored procedure, functions and much more. Today we are going to see one of the new DMV in SQL Server 2012, named “Sys.dm_server_services” which returns information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server. You can use this dynamic management view to report status information about these services.

Generally we used to use WMI script or Services.msc under control panel -> Administrative tools to see the installed services of SQL Server and the status of those services. In SQL Server 2012, you can login to SQL Server Management Studio (SSMS), if SQL Server Services is running, and execute simple DMV query to see all SQL Server related services with its status. Isn’t it useful?

Currently I am using WMI script to keep a watch on status of services which used to send me an email if any of the services found stopped in my production server and SQL Server 2012 made my life easy by providing “Sys.dm_server_services”.

Let us execute simple SELECT query on “Sys.dm_server_services”.

SELECT * FROM Sys.dm_server_services 

Here is the screen capture of above query:

You can get important information about SQL Server related services like:

  1. Service Name
  2. Service Description whether it is configured to run Automatic or manual etc.
  3. Current status like 1(stopped), 4(running), 7 (Paused) etc.
  4. Last_Startup_time
  5. Service_Account

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

List of T-SQL Running at the moment with Sys.dm_exec_requests and sys.dm_exec_sql_text in SQL Server 2008

This is something we may need many times as a administrator. Sometime when we don’t have profiler running and don’t want to go for any other route to troubleshoot  server performance, I would execute on simple T-SQL statement with the help of DMV (Sys.Exec_Requests) and DMF (Sys.dm_exec_sql_text) which can list out all the T-SQL Statement running at the moment in our database. There are many different ways to go for but this is something very quick and efficient so I keep this simple query handy all time.

SELECT
req.session_id,
req.command,
txt.text,
req.start_time,
req.status,
req.command
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) txt;

Sometime, when you suddenly started facing low performance on the server, you can run above query as a quick glance and look at the insight of SQL Server whether any heavy query is going on right now or not. This is not the only solution to performance issue, there are lot more tools and way but this could be first and quick step.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats in SQL Server 2005

Today I am happy to introduced one more article written by Mr. Mark Will regarding two very useful data management views  (DMV) in SQL Server 2005. I am sure every reader of my blog will be happy to read it as the information provided in the article could become very useful asset for everybody who are using SQL Server 2005.

Introducing SQL 2005 Two Data Management Views

by Mark Wills

In SQL 2005, new Data Management Views were introduced known as DMV’s.

I have recently been involved in some discussions as to what these views really do, and thought I might share some relatively light hearted discussion.

At first glance they give information which doesn’t reconcile. And that is why they are different, it is more the differences that become important rather than they don’t match.

They actually tell us different pieces of the index puzzle, and collectively, are very telling about your index designs (which will be a different post).

The views are :

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted and updated each time the plan is executed.

sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used and records how many times the storage engine executes a specific operation on the index.

The way I remember how to use them is by name (duh), as in “is my index useful” then usage, “is my index operating efficiently” then operational.

Let’s create a couple of test tables for this purpose :

— first a ‘heap’ table ie one with no PK or clustered index

IF object_id(‘tst_tbl_heap’,‘U’) is not null drop table tst_tbl_heap

CREATE TABLE tst_tbl_heap (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

go

— now a ‘clustered’ table

IF object_id(‘tst_tbl_indexes’,‘U’) is not null drop table tst_tbl_indexes

CREATE TABLE tst_tbl_indexes (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

ALTER TABLE tst_tbl_indexes ADD CONSTRAINT PK_tst_tbl_id PRIMARY KEY CLUSTERED(ID)

CREATE UNIQUE INDEX idx_tst_tbl_SN ON tst_tbl_indexes(SecurityNumber)

go

–OK, now lets see what we have :

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get nothing – and why not ? remember the name ? we haven’t used any indexes yet…

— but…

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s            — note : this has parameters

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

 

— does show us our two indexes, and even shows us a row for our table without an index.

— So, it is not just for indexes huh !

— now, remember our name ? operational – but are they working, no, the counts are zero.

— lets now add some data…

INSERT tst_tbl_heap(ID,FirstName,SecurityNumber) values (1,‘Mark’,‘1234567’)

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber) values (1,‘Mark’,‘1234567’)

— now lets look again at our DMV’s

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— Now we get something ! despite being called indexes, not just for indexes.

— Similarly for below we also return information

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— We wont bother anymore about the heap table, you can play with that

— the lesson was that even a table without indexes is getting in there.

— Now lets do an insert

 

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber)

SELECT 2 as id,‘Mark2’ as firstname,‘2234567’ as SecurityNumber union all

SELECT 3 as id,‘Mark3’ as firstname,‘3234567’ as SecurityNumber

 

— now lets look again at our DMV’s

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

 

— So, what happens with a select ?

SELECT * from tst_tbl_indexes where ID = 2

— now lets look again at our DMV’s

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get user_seeks=1

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— singleton_lookups=1

— so let’s now try another query

 

SELECT * from tst_tbl_indexes where ID in (1,2,3)

go

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— we get user_seeks=2

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in (‘tst_tbl_indexes’,‘tst_tbl_heap’)

go

— singleton_lookups=4 on our PK, nothing on the second (1 per selected rows above)

— and finally clean up those tables

IF object_id(‘tst_tbl_heap’,‘U’) is not null drop table tst_tbl_heap

IF object_id(‘tst_tbl_indexes’,‘U’) is not null drop table tst_tbl_indexes

 

Now I am not going to go into detail – there are plenty of other postings out there

but what I am going to do is to summarise how we can use these differences

sys.dm_db_index_usage_stats

Is cleared when service starts / reboots etc. So keep that firmly in mind

Good to help identify if an index is used – no entry unless it is

Good to help identify if a table is used – no entry unless it is

Has handy dates can help show when types of activity were last used

Has counts to help identify frequency of use

If machine has been up for the entire period that encapsulates all usage then can highlight unused indexes and consider their removal

 

sys.dm_db_index_operational_stats

Exists when table / indexes are created

Shows volumes of activity (plan, rows, pages), not just an instance

Far more detailed to help identify what type of activity

Shows row_lock_wait_count – indicating lock contention

Can measure the cost of having an index, or missing one

 

Lets look at the different types of activity that is going to be recorded:

 

SQL Statement   Read Write

Select          Yes  No

Insert          No   Yes on all indexes

Update          Yes  Yes if row affects the index

Delete          Yes  Yes

 

Armed with the above table, looking at DMV’s activity, you can soon work out where possible areas of further inspection are. Now go read books on line for a more detailed understanding of what each column is saying…

 

http://msdn.microsoft.com/en-us/library/ms188755.aspx

http://msdn.microsoft.com/en-us/library/ms174281.aspx

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah