VIEW SERVER STATE

There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it is helpful to grant this permission to non-dba personnel if they need to do any performance troubleshooting.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose sql server problems, and tune databae performance.

There are two different types of DMVs and functions:

Server-scoped DMVs and functions – These require VIEW SERVER STATE permission on the SQL Server level.
Database-scoped DMVs and functions – These require VIEW DATABASE STATE permission on each of the database.

VIEW SERVER STATE is a server level permission that grants non-sysadmin users the ability to view results of Dynamic Management Views.

Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a given database instance at a given time. Since DMVs were introduces in SQL Server 2005 and with each new release of SQL Server, Microsoft has been adding additional DMVs (Dynamic Management Views) to help troubleshoot the performance of the SQL Servers. These objects are helpful to monitor the database server in an efficient and controlled manner.

DMVs come in two subsets – Dynamic Management Views (DMVs) and DMFs (Dynamic Management Functions) and are classified as Dynamic Management Objects (DMOs).
DMVs are like any other views where you can select data from them. DMVs require values to be passed to the function just like any other functions. I will go into the details of DMFs in a separate blog.

If the user does not have sysadmin priveleges or has been granted VIEW SERVER STATE permission and tries to run the following as an example:

SELECT * FROM sys.dm_os_wait_stats

The user will see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

We run the following query to grant the user the access to the entire SQL Server Instance:

GRANT VIEW SERVER STATE TO dbuser1

This needs to be run on the master database.
Once the above script is executed successfully then that specific user will be able to view Dynamic Management Objects to do any type of performance troubleshooting.

In the case of a individual database scoped access, a user might have data reader access to the database but when the users runs this query:


SELECT * FROM sys.dm_db_partition_stats

They get an error, because this query is trying to access a DMV in the specific database.

WE need to grant VIEW DATABASE STATE for this.

We run the following query to grant the user the access to a specific database:
grant VIEW DATABASE STATE to []

USE [DatabaseName]
grant VIEW DATABASE STATE to [<DBUSer>]

Once you run the above script then the user will be able to get a result from:

SELECT * FROM sys.dm_db_partition_stats

Now lets talk about the security risks for granting VIEW SERVER STATE permission.
Yes, there is a risk in providing VIEW SERVER STATE to non-dba and non-sysadmin personnel since they aren’t supposed to see this level of intricate database information on the sql instance.
As an example: sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_stats, all provide information about execution contexts and plans, and when the user uses these with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), it will give this user information about the sql code and objects in the databases. This is usually a security violation in some organizations.

The VIEW SERVER STATE permission gives the database user unrestricted access to this information when using these DMVs. Althought they cannot change anything in the database, but they can get detailed database execution info that may not be allowed to view based on the user’s organizational responsibilites. I would analyze this type of access request with a grain of salt and ask some questions as to the security implications of this user requesting access having access to the database execution info. Also the user will be able to use DMV’s to look at queries. If the queries or some query parameters can contain confidential information that the user wouldn’t otherwise be able to see prior to granting this access – allowing VIEW SERVER STATE would allow them to do so (dateofbirth or socialscecurity #s)

Hope this helps clarify the differences between VIEW SERVER STATE and VIEW DATABASE STATE permission grants.

SQL Server build numbers

As a DBA we are asked every so often to check the sql server version on a server. When you look at the version # in ssms it looks like this:

Looking at the above SQL Server 13.0.52012.2 does not really tell me right away what is the version # of the sql server running. The most simplest method to get the sql version is to run in SSMS:

SELECT @@VERSION

This is what is get:

Microsoft SQL Server 2016 (SP2-CU2-GDR) (KB4458621) - 13.0.5201.2 (X64)   Aug 18 2018 07:38:15   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 

The above result tells me that this is SQL Server 2016 with SP2-CU2-GDR service pack applied to it.

I put together this list as a quick reference to list of the build #s and the corresponding sql versions.

SQL VersionSQL Build Numbers
SQL Server 20008.0
SQL Server 20059.0
SQL Server 200810.0
SQL Server 2008 R210.5
SQL Server 201211.0
SQL Server 201412.0
SQL Server 201613.0
SQL Server 201714.0
SQL Server 201915.0

Therefore based on the build # in the screenshot above…13.0.52012.2. The 13 tells me that this is a SQL Server 2016 installation.

I hope this page will serve as a quick reference page for you to confirm the corresponding SQL Version with the SQL Build #

SQL Server Integration Services installation failure – VS Shell Installation has failed with exit code 1638

I recently received a request to install SQL Server 2017 integration services only on a server.
After I mounted the iso media on the server, I started with the SQL Server installation.

Choose the option to install a new SQL Server instance as indicated above.

Click Next to continue…

Accept the Licensing Terms…and click next

Next, select the Integration Services only as shown below

Now once the installation started, it failed halfway during the install and gave me the following error:

VS Shell Installation has failed with exit code 1638.

After doing some research online I came across a few recommendations but the one that worked on me was to download these redistributables.

For x64 Download: https://go.microsoft.com/fwlink/?LinkId=746572

For x86 Download: https://go.microsoft.com/fwlink/?LinkId=746571

Do not try to install install both the 64 bit and the x86 bit on the server. Only choose one.
I downloaded the 64 bit version, installed it, rebooted the server.

After the reboot I restarted the sql installation and Voila! I was able to get SQL Server 2017 integration services installed on the server.

Keep in mind that once the installation is completed, you will have to find the icon for the SQL 2017 Configuration Manager and click on it. If you have multiple versions of SQL Server installed on the server as was in my case – when I opened SQL 2016 configuration manager – I was not able to see the SQL 2017 integration services. However when I opened SQL 2017 Configuration Manager

I was able to see the Integration Services I installed.

Hope this helps. Good Luck on your next SQL Installation.

Find a schema in all databases

So what is a Schema?
A SQL Server database contains multiple objects: tables, views, stored procedures, functions, indexes, triggers.

SQL Server Schemas are a logical collection of database objects. A user that owns a schema in the database are known as Schema owners. There is no restrictions on the number of objects can be in a schema.

If you need to find that default schema for a user – you need to be logged into SSMS with that user and then run the following query:

SELECT SCHEMA_NAME();

If you need to retrieve all the schema and their owners in a database, use this SQL query:

SELECT s.name AS schema_name, 
       s.schema_id, 
       u.name AS schema_owner
FROM sys.schemas s
     INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name;

On some occasion, a business user will give me a sql script to run on a database that does not have a USE clause in the script and they have no idea on which database it needs to be run on. And in most cases there are 50+ databases on the sql instance.

Here is an example of a script:

Select * from schema_name.table_name

Now usually the schema name is dbo but in some rare occasions they have a unique name for the schema. Since the user does now know which database the sql script on I use msforeachdb to find the schema name in all the databases and then I can find out in which database the schema resides on and then I can run the script the business user gave me on that database and provide the user the information they requested.

Here is the sql script to find a schema in all databases:

--This query will return a listing of all tables that matches a schema name on a SQL instance: 
DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE "%prdobj%"' 
EXEC sp_MSforeachdb @command

This has helped me tremendously in numerous occasion of tedious work on finding on which database the schema resides on. Hope this helps.

Email Alert when SQL Server is restarted

As a DBA we need to be notified when a SQL Server is restarted so that we can take a proactive reaction to identify the cause of the restart which could be an indication of underlining issues with the Server OS or the SQL Server Engine.

I have used this several years now and it has helped me avoid Severity 1 outages a few cases.

Just put this code in a sql job and make sure you change the db mail portion that pertains to your environment.

Declare @Title varchar(max)
Declare @Ebody varchar(max)
Declare @TableHead varchar(max)
Declare @TableTail varchar(max)

Select @Title = 'SQL Server Restarted - ' + @@SERVERNAME
Set NoCount On;
Set @TableTail= '</body></html>';

---HTML Layout--

Set @TableHead = '<html><head>' +
'<H4 style="color: #000000">**********This is an informational message only**********</H4>' +
 '</head>' +
 '<body>' + 'SQL Server Services have been RESTARTED : '+ '<strong>' + @@SERVERNAME + '</strong>' +
 '<p>' + ' If this was not planned, check the Server' + '</p>'

select @TableHead = @TableHead + @TableTail

WAITFOR DELAY '00:00:30'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DB_Admins',
@recipients = 'db_admins@yourdomain.com',
@body = @TableHead,
@body_format = HTML,
@subject = @Title;

Hope this helps you avoid any outages as it has helped me.

Get hard drive brand, size and serial number on a Windows Machine

A few occasions I came across a request to find the hard drive brand, size and serial number installed on a Windows OS machine.

To get the basic hard drive info and to determine some basic info about the hard drive installed on your machine, follow these steps:

Click on the Search icon on your Task Bar:

Search for Command Prompt

Item 1: Your Search Text

Item 2: Will be the search results, right click on it and click on Run as administrator

The Command Shell will open, at the prompt type in:

wmic diskdrive get model,serialNumber,size,mediaType

You will see the following info:

If you have multiple hard drives on your machine then it might take a few seconds before the results are shown.

Find Missing Index

As part of SQL Server’s processing algorithm for a query, SQL will recommend creating an index which it believes will help the query run faster – hence called Missing Index. You have to take this recommendation with a grain of salt. although SQL Server is very good at determining when an index is needed but sometimes it is not so good on the index recommendation.

I have been using this script for a few years now, donot remember where I got it from but I think it may be based off Glenn Berry’s Diagnostic Script.
The query below will show suggested missing index for a specified database. You can run it for a specified table by uncommenting the AND clause and specifying the table name.
Keep in mind that these missing index gets reset every time SQL Server is restarted, therefore if you have rebooted the server or restarted sql server recently you may not have the index stats updated yet.

USE DatabaseName
GO
 
SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

Hope this helps in resolving index issues in your database environment.

Get list of table record count

This SQL query will provide a list of tables in a database with the number of rows

select schema_name(tab.schema_id) + '.' + tab.name as [tablename],
       sum(part.rows) as [rowcount]
   from sys.tables as tab
        inner join sys.partitions as part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Each row represents each table. This will also include all the tables in the a database even the empty ones.

Get List of Table by size in Azure SQL Database

Here is a handy sql script to get a list of tables by their size in Azure SQL Database

select schema_name(tab.schema_id) + '.' + tab.name as [tablename],

    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_spacemb,

    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_spacemb

from sys.tables tab

    inner join sys.indexes ind

        on tab.object_id = ind.object_id

    inner join sys.partitions part

        on ind.object_id = part.object_id and ind.index_id = part.index_id

    inner join sys.allocation_units spc

        on part.partition_id = spc.container_id

group by schema_name(tab.schema_id) + '.' + tab.name

order by sum(spc.used_pages) desc

 

The columns you will get from this script:

tablename

size_mb

allocated_spacemb