Does AWS RDS SQL Server support Dynamic Management Views ?

Using DMVs on AWS RDS SQL Server Read more →

Get Windows OS version using t-sql

Method 1 : Query sys.dm_os_windows SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info; Method 2 : Use xp_cmdshell - although this does mean enabling xp_cmdshell , which is in many organisations as security violation exec master..xp_cmdshell 'systeminfo' Method 3: The @@version returns system and build information for the current SQL Server. SELECT @@version Method 4: xp_msver returns details about the actual build number of the server and information about the server environment.This can be very useful if you want to integrated as part of stored procedure, batch or other type of t-sql code. xp_msver Sample output 1 ProductName NULL Microsoft SQL... Read more →

How to find the local tcp port used on SQL Server

Question: How can I find the tcp port number used by a session ? I want to use a SQL Query to find this information as I don't have access to the cmdline. Answer: One method to use from within SQL Server is the sys.dm_exec_connections DMV . This SQL Server DMV returns various details about the current connections to a SQL Server Instance. This query returns connections information for all existing connections. One of the columns is the "local_tcp_port" , and when the net_transport is TCP - there will be a value in the "local_tcp_port" SELECT suser_sname() as username ,local_tcp_port... Read more →

Difference between @@servername and SERVERPROPERTY(ServerName)

I could not reliably determine the server's network name using @@servername. I executed a SELECT @@servername and the values returned were different to the SERVERPROPRTY(ServerName). I was under the impression these values are the same , could explain to me the basic difference? Answer: As a first step , this is the text straight from the MSDN site - Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer. @@SERVERNAME returns the value from sys.sysservers.... Read more →

Find Table Scans in Query Plan Cache

Question: I’m managing a SQL Server with queries running much slower than normal. I’ve analysed the sys.dm_db_index_usage_stats for heap scans. To identify the heap statistics I identified rows where the column index_id returned a value of zero. I’ve identified some heaps with a very high level of usage . How can I find the queries which are creating these large amounts of user scans? Answer: Using the sys.dm_db_index_usage_stats is a good method to identify heaps. Checking the user scans is a good way of finding clues for inefficient queries. It does not guarantee these queries are the root cause, but... Read more →