SQL SERVER – Finding Count of Logical CPU using T-SQL Script – Identify Virtual Processors

I recently received email from one of my very close friend from California. His question was very interesting. He wanted to know how many virtual processors are there available for SQL Server. He already had script for SQL Server 2008 but was mainly looking for SQL Server 2000. He made me go to my past. I found following script from my old emails (I have no reference listed along with it, so not sure the original source).

-- Identify Virtual Processors in for SQL Server 2005, 2008, 2008R2, 2012
SELECT cpu_count
FROM sys.dm_os_sys_info
GO
-- Identify Virtual Processors in for SQL Server 2000
CREATE TABLE #TempTable
([Index] VARCHAR(2000),
[Name] VARCHAR(2000),
[Internal_Value] VARCHAR(2000),
[Character_Value] VARCHAR(2000)) ;
INSERT INTO #TempTable
EXEC xp_msver;
SELECT Internal_Value AS VirtualCPUCount
FROM #TempTable
WHERE Name = 'ProcessorCount';
DROP TABLE #TempTable
GO

Yesterday I shared on facebook page about I am writing this blog post, SQL Server Expert Simran Jindal shared following script which is applicable to SQL Server 2005 and later versions. I just got update from her that this query is of my dear friend and SQL Server MVP Glenn Berry. Thanks Glenn.

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1048576 AS Physical_Memory_in_MB,
sqlserver_start_time, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info

If know any other reliable method to get the count of logical CPU, please share that in comment and I will update this blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – DVM sys.dm_os_sys_info Column Name Changed in SQL Server 2012

Have you ever faced situation where something does not work? When you try to fix it ‑ you enjoy fixing it and started to appreciate the breaking changes. Well, this is exactly I felt yesterday. Before I begin my story, I want to candidly state that I do not encourage anybody to use * in the SELECT statement.

deleteerase SQL SERVER   DVM sys.dm os sys info Column Name Changed in SQL Server 2012

One of the my DBA friends, who always used my performance tuning script, sent me an email yesterday with the following question –

“Every time I want to retrieve OS related information in SQL Server, I use DMV sys.dm_os_sys_info. I just upgraded my SQL Server edition from 2008 R2 to SQL Server 2012 RC0, and it suddenly stopped working. Well, this is not the production server; so the issue is not big yet – but, eventually I need to resolve this error. Any suggestion?”

The funny thing about this was that the original email was very long, but it did not talk about what the exact error is besides that the query is not working. I think this is the disadvantage of being too friendly on email sometimes. Well, nevertheless, I quickly looked at the DMV on my SQL Server 2008 R2 and SQL Server 2012 RC0 version.

To my surprise, I found out that there were few columns that are renamed in SQL Server 2012 RC0. Usually, when people see breaking changes, they do not like it; but when I see these changes, I was happy as new names were meaningful, and additionally, their new conversion is much more practical and useful.

Here are the columns’ previous names:

 

Previous Column Name New Column Name
physical_memory_in_bytes physical_memory_kb
bpool_commit_target committed_target_kb
bpool_visible visible_target_kb
virtual_memory_in_bytes virtual_memory_kb
bpool_commited committed_kb

If you read it carefully, then you will notice that new columns now display few results in the kb, whereas earlier results were in bytes. When I see the results in bytes, I always get confused as I cannot guess what exactly it will convert into. I like to see results in kb, and I am glad that new columns are now displaying the results in kb.

I sent the details of the new columns to my friend and ask him to check the columns used in application. From my comment, he immediately realized why he was facing such an error and fixed it.

Overall, all is well at the end, and I learned something new.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – Three DMVs – sys.dm_server_memory_dumps – sys.dm_server_services – sys.dm_server_registry

In this blog post we will see three new DMVs which are introduced in Denali. The DMVs are very simple and there is not much to describe them. So here is the simple game. I will be asking a question back to you after seeing the result of the each of the DMV and you help me to complete this blog post.

SELECT *
FROM sys.dm_server_memory_dumps

Above DMV returns following result.

memorydump SQL SERVER   Denali   Three DMVs   sys.dm server memory dumps   sys.dm server services   sys.dm server registry

SELECT *
FROM sys.dm_server_services

Above DMV returns following result.

denaliservices SQL SERVER   Denali   Three DMVs   sys.dm server memory dumps   sys.dm server services   sys.dm server registry

SELECT *
FROM sys.dm_server_registry

Above DMV returns following result.

denaliregistry SQL SERVER   Denali   Three DMVs   sys.dm server memory dumps   sys.dm server services   sys.dm server registry

Now here is the question for you – how will you use this DMV in your application. One thing for sure is that this makes it easier to find out various information. We can easily know which services are running and what was the start time etc but also where exactly you will use this in production server?

Response to Question:
SQLConcept – Feodor Georgiev has given excellent summary on this subject. A Must Read

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

SELECT *
FROM fn_helpcollations()

helpcollations SQL SERVER   Detecting Database Case Sensitive Property using fn helpcollations()

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

SELECT 1
WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:

SELECT 1
FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – DMV – sys.dm_os_windows_info – Information about Operating System

One more quick introduction to DMV for Denali. Following DMV provides information about Windows Operating System. Here is the quick example of the same.

This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.

SELECT *
FROM sys.dm_os_windows_info;

Here is the screenshot of the same:

denalidmv SQL SERVER   Denali   DMV   sys.dm os windows info   Information about Operating System

Here is my question back to you – where would you use this stored procedure in your application? What is your preferred method to know details about Windows? One last question – what is 1033 in the last column of the table result and what is 1033 represent?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – DMV – sys.dm_os_volume_stats – Information about operating system volume

SQL Server Denali has many new interesting feature – one of the interesting feature is New DMVs.

This DMV returns information about the operating system volume (directory) on which the specified databases and files are stored. Here is the quick example I have created for the same.

SELECT DB_NAME(f.database_id) DatabaseName,
f.FILE_ID, size DBSize, file_system_type,
volume_mount_point, total_bytes, available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID);

Here is the screenshot of the same:

volumeset SQL SERVER   Denali   DMV   sys.dm os volume stats   Information about operating system volume

In the result set we can see the file system and volume database is mounted on as well database size.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Denali – DMV Enhancement – sys.dm_exec_query_stats – New Columns

SQL Server version next Denali has lots of enhancements. Some of the enhancements are just game changing and overcomes needs of more coding to do the same thing.

Similar function DMV is sys.dm_exec_query_stats. There are four new columns added to this DMV. I have often used this DMV to check recently ran query, their execution plan by joining more DMVs to it. However, there was also need of knowing how many rows my queries have returned.

This DMV is enhanced with four more queries.

total_rows – Total number of rows returned by query
last_rows – Number of the rows return by the last execution of the query
min_rows – Minimum numbers of the rows returned by the query since it is compiled
max_rows – Maximum numbers of the rows returned by the query since it is compiled

Here see the quick example of the columns:

SELECT qs.execution_count,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(
CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;

If you run above query it will give us different result based on your server’s workload.

denali dm1 SQL SERVER   Denali   DMV Enhancement   sys.dm exec query stats   New Columns

You can see that the above result set – it displays how many time query has executed and how new columns (total_rows, last_rows, min_rows and max_rows) returns result based on the query.

Now here is the question back to you – if you have downloaded Denali and installed it, I would like to see you use this new column and come up with some more creative usage.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Author’s Book is Available in India and USA

I am feeling very good to write this short blog post. My book is now officially available on in India and USA.

In India you can get it from Flipkart – http://bit.ly/pinalbook

In USA you can get it from Amazon – http://amzn.to/pb49jq

This book is just like this blog and contains all the complex subject in very simple manner. I am confident that you will for sure like this book if you like this blog.

Here is quick video shot by my wife when I was reading my own book. See the original post to see the video.

Here is quick secret for you – there is big surprise in September for all those who own this book.

joes2pros4 SQL SERVER   Authors Book is Available in India and USA

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Details for Statistics of Whole Database – DMV – T-SQL Script

statistics Img SQL SERVER   Find Details for Statistics of Whole Database   DMV   T SQL ScriptI was recently asked is there a single script which can provide all the necessary details about statistics for any database. This question made me write following script. I was initially planning to use sp_helpstats command but I remembered that this is marked to be deprecated in future. Again, using DMV is the right thing to do moving forward. I quickly wrote following script which gives a lot more information than sp_helpstats.

USE AdventureWorks
GO
SELECT DISTINCT
OBJECT_NAME(s.[object_id]) AS TableName,
c.name AS ColumnName,
s.name AS StatName,
s.auto_created,
s.user_created,
s.no_recompute,
s.[object_id],
s.stats_id,
sc.stats_column_id,
sc.column_id,
STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated
FROM sys.stats s JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id
JOIN sys.partitions par ON par.[object_id] = s.[object_id]
JOIN sys.objects obj ON par.[object_id] = obj.[object_id]
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND (s.auto_created = 1 OR s.user_created = 1);

stats DMV SQL SERVER   Find Details for Statistics of Whole Database   DMV   T SQL Script

If you have better script to retrieve information about statistics, please share here and I will publish it with due credit.

Update: Read follow up excellent blog post by Jason Brimhall.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Pending IO request in SQL Server – DMV

I received following question:

“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?”

Very interesting question and indeed answer is very interesting as well.

Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.

USE DATABASE
GO
SELECT vfs.database_id, df.name, df.physical_name
,vfs.FILE_ID, ior.io_pending
FROM sys.dm_io_pending_io_requests ior
INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs
ON (vfs.file_handle = ior.io_handle)
INNER JOIN sys.database_files df ON (df.FILE_ID = vfs.FILE_ID)

 SQL SERVER   Pending IO request in SQL Server   DMV

I keep this script handy as it works like magic every time. If you use any other script please post here and I will post it with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)