Archive

Posts Tagged ‘DBA Queries’

How to get orphaned logins for all databases?

April 4, 2016 2 comments

Following script gives you all user database users, that do not have a server login mapped or where database user name does not match server login name.  This is follow up post to How get all users and their role mappings from all databases? I posted few days ago.

IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO
 
CREATE TABLE #Output (DatabaseName VARCHAR(255), UserLoginSID varbinary(128), ServerLoginName VARCHAR(255), DatabaseUserName VARCHAR(255), UserType VARCHAR(50))
GO
 
sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DP.sid
      , SP.name
      , DP.name
      , DP.type_desc
  FROM sys.database_principals DP
  LEFT JOIN sys.server_principals SP
    ON DP.sid = SP.sid
 WHERE DP.type_desc IN (''SQL_USER'',''WINDOWS_USER'')
   AND DP.sid IS NOT NULL'
GO
 
  SELECT *
    FROM #Output
   WHERE ServerLoginName IS NULL
      OR ServerLoginName <> DatabaseUserName
ORDER BY DatabaseName, ServerLoginName
GO

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Categories: Administration, Security Tags:

How get all users and their role mappings from all databases?

April 2, 2016 5 comments
IF EXISTS(SELECT * FROM tempdb.sys.tables WHERE name like '#Output%')
   DROP TABLE #Output
GO

CREATE TABLE #Output (DatabaseName VARCHAR(255), RoleName VARCHAR(255), UserName VARCHAR(255))
GO

sp_msforeachdb '
INSERT INTO #Output
SELECT  ''?''
      , DR.name
      , DP.name
FROM [?].sys.database_principals  AS DR
JOIN [?].sys.database_role_members AS DRM
   ON DR.principal_id = DRM.role_principal_id
JOIN [?].sys.database_principals AS DP
   ON DP.principal_id = DRM.member_principal_id
WHERE (DP.name <> ''dbo'')'
GO

SELECT *
  FROM #Output
GO
Categories: Administration, Security Tags:

Using sys.dm_io_virtual_file_stats DMF to get IPOS and BPS

September 26, 2012 9 comments

SQL Server keeps track of all the reads and writes it submits to the operating system (OS).  It records this information in sys.dm_io_virtual_file_stats DMF.  We can use the information recorded there in conjunction with the when the SQL was last restarted to figure total number of Input-Output Per Seconds (IOPS) and Bytes Per Second (BPS) generated by SQL Server.

You ask… WHY? Why do I care?

Well let’s say you are having problem with disk sub system, you have multiple databases you are trying to figure out which database generated the most IO?  Or better yet which file did the most work …

You say AHA! I can just select everything virtual file stats DMF.  True, True you can, you can find out which file has the most activity.  But now you go to your SAN guy, you SAN is not good enough for my SQL Server.

SAN guy in return is going to say “Nope, I don’t see any issues…”

So now what?

This is where this information comes useful, you need to present to the SAN guy with total number of IOPS and Throughput per second SQL Server is generating.  Then ask the SAN guy can the current configuration support you IOPS workload?  Can you current HBA configuration or iSCSI configuration support the current workload?

So this is where this script comes in, up_Get_IOPS_n_BPS.  Try it out and tell me what you think, I welcome comments.

SET NOCOUNT ON

DECLARE @SQLRestartDateTime Datetime
DECLARE @TimeInSeconds Float

SELECT @SQLRestartDateTime = create_date FROM sys.databases WHERE database_id = 2

SET @TimeInSeconds = Datediff(s,@SQLRestartDateTime,GetDate())

PRINT 'Input-Output Per Second and Bytes Per Second by Database and File'
PRINT ''

    SELECT   DB_NAME(IVFS.database_id) AS DatabaseName
           , MF.type_desc AS FileType
           , MF.name AS VirtualFileName
           , MF.Physical_Name AS StorageLocation
           , ROUND((num_of_reads + num_of_writes)/@TimeInSeconds,4) AS IOPS
           , ROUND((num_of_bytes_read + num_of_bytes_written)/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS
      JOIN sys.master_files MF ON IVFS.database_id = MF.database_id AND IVFS.file_id = MF.file_id
  ORDER BY DatabaseName ASC, VirtualFileName ASC

  PRINT 'Input-Output Per Second and Bytes Per Second by Database'
  PRINT ''

    SELECT   DB_NAME(IVFS.database_id) AS DatabaseName
           , ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS
           , ROUND((SUM(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS
  GROUP BY db_name(IVFS.database_id)
  ORDER BY DatabaseName ASC

  PRINT 'Total Input-Output Per Second and Bytes Per Second on Server'
  PRINT ''

    SELECT   ROUND((SUM(num_of_reads + num_of_writes))/@TimeInSeconds,4) AS IOPS
           , ROUND((sum(num_of_bytes_read + num_of_bytes_written))/@TimeInSeconds,2) AS BPS
      FROM sys.dm_io_virtual_file_stats(null,null) IVFS

  SET NOCOUNT OFF

I will be adding additional scripts for this that let you calculate IOPS generated in last hour or last day also, maybe the heavy workload happened only recently.  Buttttt… that’s for another day.

Example Output – Input-Output Per Second and Bytes Per Second by Database

Database Name IOPS BPS
AdventureWorks2012 0.0002 8.76
DB1 0.0001 5.15
DB2 0.0001 5.61
BackupDemo 0.0002 7
DBAGoodies 0.0002 6.85
master 0.0003 9.66
model 0.0004 21.05
msdb 0.0002 10.15
tempdb 0.0001 5.52

Example – Total Input-Output Per Seconds and Bytes Per Seconds on Server

IOPS BPS
0.0017 79.75

Update Oct 1, 2012:
Fixed the SQL Statement to get the last restart date. Added “Where database_id = 2”. Since TempDB is recreated every time, we can use that to calculate the our start up time.

A Microsoft SQL Server DMV/DMF Cheat-Sheet

May 25, 2012 1 comment

I am asked this question by Microsoft SQL Server customers again and again: “What dynamic management views (DMV) or dynamic management functions (DMF) should I use?” The short answer is, as many as you need. The detailed answer is, well, it depends. There are well over 150 DMV/DMFs spread across 20 some categories in 2012 now, and it is a daunting challenge trying to remember all these. I don’t think I have used all of these, I don’t think I ever will. The use of these really stems from what issues you are trying to deal with or what information you need from SQL Server. Since the use for most DMVs is scenarios based, I am listing here DMV/DMFs that I think every DBA should get familiar with. Now I am not saying this is an exhaustive list, but I find it a good starting point.

SQL Server DMV/DMFs that every DBA should know

SQL Server 2012 Dynamic Management Views and Functions

Execution Related

  • sys.dm_exec_connections
  • sys.dm_exec_sessions
  • sys.dm_exec_requests
  • sys.dm_exec_cached_plans
  • sys.dm_exec_query_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_query_stats

Index Related

  • sys.dm_db_index_physical_stats
  • sys.dm_db_index_usage_stats

SQL Server Operating System

  • sys.dm_os_performance_counters
  • sys.dm_os_schedulers
  • sys.dm_os_nodes
  • sys.dm_os_waiting_tasks
  • sys.dm_os_wait_stats

I/O Related

  • sys.dm_io_virtual_file_stats

Using DMV/DMFs

Instead of just giving you random SQL statements against the DMV/DMFs listed above, I am going to walk through the DMV and build the corresponding SQL statement based on information I want to collect. For starters we will look to answer the following question: who is connected to SQL Server? We have in particular two DMVs to give us that information; sys.dm_exec_connections and sys.dm_exec_sessions. The sys.dm_exec_connections returns information for only user connections, whereas sys.dm_exec_sessions returns information for both user connections and system sessions. There is one-to-one relationship between the two DMVs. When looking at just the sys.dm_exec_sessions we can identify the system sessions by looking at the session_id column; values greater than or equal to 51 are user connections and be low are system processes.

Statement #1: Get a Listing of All Connections:
SELECT *
  FROM sys.dm_exec_connections
Statement #2: Get a Listing of All Sessions
SELECT *
  FROM sys.dm_exec_sessions

Both statements return everything from the DMV, sample output is below.

Output of Statement #1:

clip_image002

Output of Statement #2:

clip_image004

My default DMV returns too much information, so I am going to re-write both statements to return the information I am most interested in.

Statement #3: Get a listing of all user connections:
    SELECT   c.session_id
           , c.auth_scheme
           , c.node_affinity
           , s.login_name
           , db_name(s.database_id) AS database_name
           , CASE s.transaction_isolation_level
             WHEN 0 THEN 'Unspecified'
             WHEN 1 THEN 'Read Uncomitted'
             WHEN 2 THEN 'Read Committed'
             WHEN 3 THEN 'Repeatable'
             WHEN 4 THEN 'Serializable'
             WHEN 5 THEN 'Snapshot'
             END AS transaction_isolation_level
           , s.status
           , c.most_recent_sql_handle
      FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
        ON c.session_id = s.session_id
So I have combined Statement #1 and Statement #2 into above SQL statement, selecting the fields with key information.
Field Name Reason For Selection
auth_scheme Let us know what protocol they are coming in on: NTML, KERBEROS, or SQL.
node_affinity This is important to find out who is using the DAC connection (I’ll explain below).
transaction_isolation_level I want to know this, especially if I am dealing with a Blocking Issue and it is because users are using Serializable isolation level.
most_recent_sql_handle Now yes we still have DBCC INPUTBUFFER (); however I like this a lot better because I can join into other DMVs; will show you examples later.

Note we are only getting user connection information; in case we needed details for system sessions we will have to change INNER JOIN to RIGHT OUTER JOIN instead. Now we know who is connected, but we also want to know what are they doing? The STATUS field in the sys.dm_exec_sessions can tell us if the session is actively running something or is sleeping. Sleeping means the last task on the session has completed and is waiting for next batch of work (aka Idle Connection). And if it’s running we can find out what is the being run by the session, by diving into sys.dm_exec_requests; again I am going to expand Stmt3 to include new DMV now.

Statement #4: Get a listing of all user connections with their request details:
    SELECT   c.session_id
           , c.auth_scheme
           , c.node_affinity
           , r.scheduler_id
           , s.login_name
           , db_name(s.database_id) AS database_name
           , CASE s.transaction_isolation_level
             WHEN 0 THEN 'Unspecified'
             WHEN 1 THEN 'Read Uncomitted'
             WHEN 2 THEN 'Read Committed'
             WHEN 3 THEN 'Repeatable'
             WHEN 4 THEN 'Serializable'
             WHEN 5 THEN 'Snapshot'
             END AS transaction_isolation_level
           , s.status AS SessionStatus
           , r.status AS RequestStatus
	   , CASE WHEN r.sql_handle IS NULL THEN
	        c.most_recent_sql_handle
             ELSE
	        r.sql_handle
	     END AS sql_handle
           , r.cpu_time
	   , r.reads
	   , r.writes
	   , r.logical_reads
	   , r.total_elapsed_time
      FROM sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
        ON c.session_id = s.session_id
 LEFT JOIN sys.dm_exec_requests r
        ON c.session_id = r.session_id

I think this SQL statement gives us enough information to see what is going on in SQL Server. If we needed to dig in further we can use other DMVs or this one. So one question I often get asked is why use all these DMV? Why not sys.sysprocessesor sp_who or sp_who2?

  • Sysprocesses will be phased on in a future version, so it’s better to get use to the DMV before it is completely gone.
  • Sp_who and sp_who2 do not provide as much information as the DMV/DMF. I have taken out a lot of the information from each DMV/DMF, but after you know what you are looking for you can always dig further into the DMV/DMFs.

In this article we looked at getting information on who is connected and what request they are running. In my next post I’ll talk about how to get the T-SQL Code they are running and even the execution plans for those statements.

Here’s an archive containing the SQL script in this post: DMVDMFCheatSheet_Part1.zip
Also available at OpsVault.com and MSDN Blog.

Getting List of Top Queries by Query Hash

May 16, 2012 Leave a comment

Starting SQL 2008, we have had Query Hash column in sys.dm_exec_query_stats.  It helps group common queries together, because when SQL Server is generating execution plan or caches queries it bases it on a HASH value it calculates.  However, if you’re application is mostly using T-SQL (e.g. not Stored Procedures); then the HASH value be different every time.  So the Query Stats DMV couldn’t provide proper grouping until 2008; script below takes advantage of that and gives you total stats for key columns.

-- SQLCanada, Mohit K. Gupta
-- Script from https://sqlcan.wordpress.com
-- Last Updated: May 12, 2012
------------------------------------------

WITH QSD (SQLStatement, PlanHandle, NumOfExecutions, Duration_ms, CPU_ms, Reads, Writes, QueryHash)
AS (SELECT   SUBSTRING(st.text,
                       (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qs.plan_handle AS PlanHandle
            , execution_count AS NumOfExecutions
            , total_elapsed_time/1000 AS Duration_ms
            , total_worker_time/1000 AS CPU_ms
            , total_logical_reads AS Reads
            , total_logical_writes AS Writes
            , query_hash AS QueryHash
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
      WHERE query_hash != 0x0000000000000000)

  SELECT QSD.QueryHash,
         MIN(QSD.SQLStatement) AS SQLStatement,
	      MIN(QSD.PlanHandle)   AS PlanHandle,
         SUM(QSD.NumOfExecutions) AS TotalNumOfExecutions,
         SUM(QSD.Duration_ms)/SUM(QSD.NumOfExecutions) AS AvgDuration_ms,
         SUM(QSD.CPU_ms)/SUM(QSD.NumOfExecutions) AS AvgCPU_ms,
         SUM(QSD.Reads)/SUM(QSD.NumOfExecutions) AS AvgReads,
         SUM(QSD.Writes)/SUM(QSD.NumOfExecutions) AS AvgWrites
    FROM QSD
GROUP BY QueryHash

Example Output:

image

If you notice I don’t have any kind of sort or Top 50 function.  Reason is I don’t want to tax SQL Server; after this list is produced I can pull it into Microsoft Excel for analysis.  On a 32-bit system you can get away with doing this right on server; however on 64-bit server with 128GB of memory, the data set returned can be large trying to do top 50 or other sorting can be costly.

Also please note I didn’t include sys.dm_exec_query_plan for similar reasons.  After I find the statement I want to focus on I’ll use the value of PlanHandle to pull out the individual plans.

Getting List of All Files and Their Growth Information

May 15, 2012 Leave a comment

SQL Server defaults to growing data files by 1MB and transaction logs by 10%; neither of those are best practice.  If your database is small they don’t hurt, however as database grows and gets larger, there will be performance issues.  Growing a 1TB size database 1MB at a time is painful.  And if you have 100GB transaction log file setting it to grow by 10% can be painful also; because you might run of disk space quickly.  Another concern is any growth events take a while to execute, so we want to minimize the blocking cause by these.

Best time to grow a database is off-hours, manually, auto growth is there for emergencies not as a replacement for a DBA.  Following script helps identify databases with percent growth or growth set to 1MB.

-- SQLCanada, Mohit K. Gupta
-- Script from https://sqlcan.wordpress.com
-- Last Updated: May 12, 2012
------------------------------------------   

  SELECT DB_NAME(database_id) AS DatabaseName,
         CASE WHEN type_desc = 'ROWS' THEN
            'Data'
         ELSE
            'Log'
         END AS FileType,
         name AS FileName,
         physical_name AS FileLocation,
         CASE WHEN is_percent_growth = 1 THEN
            CAST(growth AS VARCHAR) + '%'
         ELSE
            'MB'
         END AS GrowthSetting,
         CASE WHEN is_percent_growth = 1 THEN
            CAST((((size*growth)/100)*8)/1024 AS BIGINT)
         ELSE
            (growth * 8)/1024
         END AS GrowthSize_MB,
         DATABASEPROPERTYEX(db_name(database_id),'Recovery') AS RecoveryModel,
         DATABASEPROPERTYEX(db_name(database_id),'Collation') AS DBCollation
    FROM sys.master_files
ORDER BY GrowthSize_MB DESC

Example Output:

image

How to find the log file usage for a transaction in SQL Server 2005?

March 30, 2012 Leave a comment

Word of caution before you try/read this: I am using undocumented function to read the trace file.  I do not grantee the results to be accurate, please use it your own risk. 

Client was wondering if there was anyway we can figure out how much log file is being used for each statement executed on SQL Server?  The simple answer is NO.  We can look at the Log Bytes Flush/sec in the SQL Server: Database object; however it doesn’t really correlate back to which transaction did the work.  So I decided to some random testing using fn_trace_gettable (Used to read Trace Files) and fn_dblog (Undocumented Command).

First open and setup a Trace with with following events/columns; you can add more events if you think it is needed for example StmtCompleted.

Please note this is minimal data needed, you are free to add additional data however it will not be needed for purpose of finding T-Log file usage.

Next turn on the trace and execute some random statements that will be logged to T-Log. After you have finished capturing your trace events, stop the trace and save the trace file where you can read it using fn_trace_gettable function.

Now execute following statement in the context of the DB we want to track log space usage:

WITH TranTotal (TransactionID, TotalLogFileUsage, OperationCount)
AS
(  SELECT   [Transaction ID]
          , SUM([Log Record Length])
		  , COUNT(*)
     FROM sys.fn_dblog(null,null)
 GROUP BY [Transaction ID])

SELECT   dblog.[Transaction ID]
		        , OperationCount
				, T.TextData
				, TotalLogFileUsage
				, dblog.[Begin Time]
           FROM sys.fn_dblog(null,null) dblog
INNER JOIN TranTotal TT
	         ON dblog.[Transaction ID] = TT.TransactionID
LEFT OUTER JOIN sys.fn_trace_gettable('C:\temp\TraceDataForCalculation.trc',default) T
ON dblog.[Begin Time] = T.StartTime

From which you’ll get output like below; please note one batch can have more the one transaction.  I am not quite sure how it is deteremined batch id, if I find more on this I will post.

Where the TextData is NULL my Trace File did not have any data, since I justed my regular test database and I have never done back on it, I have transactions in there from before.

PS I am sure Extended Events makes this easy, rightt now 2005 was goal.  I’ll post on 2008 later when I have “spare time” :p.

Getting List of Fragmented Indexes in a Database

November 9, 2011 2 comments

Index fragmentation is naturally happens as an action of insert, deletes and updates.  It is important to manage this fragmentation, if not you can have performance issue with range type queries.

  SELECT   OBJECT_NAME(IPS.OBJECT_ID)       AS TableName
         , CASE WHEN i.name IS NULL THEN
              'HEAP TABLE, NO INDEXES'
           ELSE
              i.name
           END                              AS IndexName
         , index_type_desc                  AS IndexType
         , index_depth
         , avg_fragmentation_in_percent
         , page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
    JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
ORDER BY avg_fragmentation_in_percent

Finding All Plans/SQL Statements that executed in Parallel

October 21, 2011 Leave a comment
       WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
     SELECT   query_plan                                            AS CompleteQueryPlan
            , n.value('(@StatementText)[1]', 'VARCHAR(4000)')       AS StatementText
            , n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)')    AS StatementOptimizationLevel
            , n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
            , n.query('.')                                          AS ParallelSubTreeXML
       FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')     AS qn(n)
      WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1

References: #21

Statement copied from The Rambling DBA: Jonathan Kehayias Blog

Get Top 50 SQL Statements/Query Plans

October 21, 2011 1 comment

Finding out what statements causing grief on SQL Server has become significantly easier with SQL Server 2005+.  I am using some of the DMV/DMF to pull this information together.  Please note on a 64-bit system with lots of memory this can take a bit of time to run because of Sort & pulling execution plan information.  If that is an issue, I recommend getting the full information without sort by and dumping it to a temp table.  From there do your analysis, here are few options for running this statement:

Following query returns everything it is very expensive and takes a while to complete, even if you dump it to tempdb it can take 10+ mintutes on systems with lots of memory.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qp.query_plan                                         AS QueryPlan
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC

Same code, but I have taken out the execution plan, so it should execute faster. The idea is once you know which queries you want to address those are the only plans you will bring up; making it faster. However this method if you save the data, there is no grantee the plan will be there in the future.

-- Get Top 50 Statements that have been recorded in SQL Server since last restart.
--

DECLARE @Top50By INT

-- 1 = Duration, 2 = CPU, 3 = Reads
SET @Top50By = 1

     SELECT   TOP 50
              SUBSTRING(st.text,
                        (qs.statement_start_offset/2)+1,
                        ((CASE qs.statement_end_offset WHEN -1 THEN
                             DATALENGTH(st.text)
                          ELSE
                             qs.statement_end_offset
                          END - qs.statement_start_offset)/2) + 1)  AS SQLStatement
            , qs.plan_handle										AS PlanHandle
            , execution_count                                       AS NumOfExecutions
            , (total_elapsed_time/execution_count)/1000             AS AVGDuration_ms
            , (total_worker_time/execution_count)/1000              AS AVGCPU_ms
            , (total_logical_reads/execution_count)                 AS AVGReads
            , (total_logical_writes/execution_count)                AS AVGWrites
       FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st
   ORDER BY CASE
            WHEN @Top50By = 1 THEN
               (total_elapsed_time/execution_count)/1000
            WHEN @Top50By = 2 THEN
               (total_worker_time/execution_count)/1000
            WHEN @Top50By = 3 THEN
               (total_logical_reads/execution_count)
            END DESC
%d bloggers like this: