2013-02-16

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

Recently I had to check a quite denormalized database at the end of its development phase.

Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).

UPDATE: system objects skipped

;WITH cte
AS (
 SELECT c.NAME cl_name
  ,Schema_name(t.schema_id) + '.' + t.NAME tb_name
  ,p.NAME [type]
  ,c.max_length
  ,c.scale
  ,c.PRECISION
 FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 INNER JOIN sys.types p ON p.user_type_id = c.user_type_id
  AND p.system_type_id = c.system_type_id
 WHERE
  NOT (
     EXISTS (SELECT 1 FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND EP.name = 'microsoft_database_tools_support')
     OR t.is_ms_shipped = 1
  )
 )
SELECT DISTINCT x.*
FROM cte x
INNER JOIN cte y ON y.tb_name != x.tb_name
 AND y.cl_name = x.cl_name
 AND (
  y.max_length != x.max_length
  OR y.PRECISION != x.PRECISION
  OR y.scale != x.scale
  OR y.[type] != x.[type]
  )
ORDER BY 1

2012-08-19

Active Processes


ActiveProcesses.zip

This time I'll show you a script developed to cature running processes details.

There are a number of similar scripts out of there, but I've tried to use a single query based on DMVs only (no more sys.sysprocesses).

Exluding the deprecated sys.sysprocesses was the only tricky task, because there is no straight way to obtain the database_id unless we are using Sql Server 2012 which added this column to sys.dm_exec_sessions.

Fortunately sys.dm_tran_locks contains the information we need.

SET TRANSACTION isolation level READ uncommitted

SELECT CASE
         WHEN ER.total_elapsed_time > 0 THEN
         CONVERT(VARCHAR, (ER.total_elapsed_time - ER.total_elapsed_time %
         3600000) /
         3600000)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 60000) % 3600000 /
         60000), 2)
         + ':'
         + RIGHT('00' + CONVERT(VARCHAR, (ER.total_elapsed_time -
         ER.total_elapsed_time
         % 1000) % 60000 / 1000), 2)
         + '.'
         + RIGHT('000' + CONVERT(VARCHAR, ER.total_elapsed_time % 1000), 3)
       END
       elapsed_time,
       ES.session_id,
       NULLIF(ER.blocking_session_id, 0)
       blocking_session_id,
       ES.[status],
       Db_name(COALESCE(ER.database_id, TL.resource_database_id, 1))
       database_name,
       ER.cpu_time,
       ER.reads,
       ER.writes,
       ER.logical_reads,
       ER.wait_time,
       ER.wait_type,
       ER.last_wait_type,
       ES.total_elapsed_time,
       ES.cpu_time
       total_cpu_time,
       ES.reads                                                      total_reads
       ,
       ES.writes
       total_writes,
       ES.logical_reads
       total_logical_reads,
       CASE
         WHEN Isnull(ER.open_transaction_count, 0) > 0 THEN
         ER.open_transaction_count
         WHEN ST.open_tran > 0 THEN ST.open_tran
       END
       open_transaction_count,
       CASE Isnull(ER.transaction_isolation_level,
            ES.transaction_isolation_level)
         WHEN 0 THEN 'Unspecified'
         WHEN 1 THEN 'ReadUncomitted'
         WHEN 2 THEN 'ReadCommitted'
         WHEN 3 THEN 'Repeatable'
         WHEN 4 THEN 'Serializable'
         WHEN 5 THEN 'Snapshot'
       END
       transaction_isolation,
       AT.name
       transaction_name,
       CASE AT.transaction_type
         WHEN 1 THEN 'read/write'
         WHEN 2 THEN 'read-only'
         WHEN 3 THEN 'system'
         WHEN 4 THEN 'distributed'
       END
       transaction_type,
       CASE AT.transaction_state
         WHEN 0 THEN 'not completely initialized'
         WHEN 1 THEN 'initialized but not started'
         WHEN 2 THEN 'active'
         WHEN 3 THEN 'read-only transaction has ended'
         WHEN 4 THEN 'commit initiated'
         WHEN 5 THEN 'prepared and waiting resolution'
         WHEN 6 THEN 'committed'
         WHEN 7 THEN 'being rolled back'
         WHEN 8 THEN 'been rolled back'
       END
       transaction_state,
       ES.last_request_start_time,
       ES.last_request_end_time,
       ES.[host_name],
       ES.login_time,
       ES.login_name,
       ES.[language],
       ES.date_format,
       ES.date_first,
       ES.[program_name],
       ER.command,
       ET.[text]
       sql_statement
FROM   sys.dm_exec_connections EC
       INNER JOIN sys.dm_exec_sessions ES
               ON ES.session_id = EC.session_id
       LEFT JOIN sys.dm_exec_requests ER
              ON ER.session_id = EC.session_id
       LEFT JOIN (SELECT Count(*) open_tran,
                         XT.session_id
                  FROM   sys.dm_tran_session_transactions XT
                  GROUP  BY XT.session_id) ST
              ON ST.session_id = ES.session_id
       LEFT JOIN sys.dm_tran_active_transactions AT
              ON AT.transaction_id = ER.transaction_id
       OUTER apply sys.Dm_exec_sql_text(Isnull(ER.sql_handle,
                                        EC.most_recent_sql_handle)) ET
       OUTER apply (SELECT TOP 1 XL.resource_database_id
                    FROM   sys.dm_tran_locks XL
                    WHERE  XL.resource_type = 'DATABASE'
                           AND XL.request_session_id = EC.session_id) TL
WHERE  EC.most_recent_sql_handle > 0x00
ORDER  BY ER.total_elapsed_time DESC,
          EC.session_id


This script will be included in the next version of SqlDbAid.

2012-04-09

Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).


SELECT
 CASE
  WHEN P.class IN (0, 3) THEN P.class_desc
  ELSE O.type_desc
 END type_desc,
 CASE
  WHEN P.class IN (0, 3) THEN ''
  ELSE SCHEMA_NAME(O.schema_id)
 END object_schema,
 CASE
  WHEN P.class = 3 THEN SCHEMA_NAME(P.major_id)
  WHEN P.class = 1 THEN OBJECT_NAME(P.major_id)
  ELSE ''
 END object_name,
 ISNULL(C.name, '') column_name,
 P.class_desc,
 USER_NAME(P.grantee_principal_id) grantee,
 P.permission_name,
 P.state_desc,
 USER_NAME(P.grantor_principal_id) grantor,
 CASE P.state
  WHEN 'W' THEN 'GRANT'
  ELSE P.state_desc
 END + ' ' +
 CASE
  WHEN P.class = 0 THEN P.permission_name + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default
  WHEN P.class = 3 THEN P.permission_name + ' ON SCHEMA::' + QUOTENAME(SCHEMA_NAME(P.major_id)) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  WHEN P.minor_id <> 0 THEN P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' (' + QUOTENAME(C.name) + ') TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
  ELSE P.permission_name + ' ON ' + QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.name) + ' TO ' + QUOTENAME(USER_NAME(P.grantee_principal_id))
 END +
 CASE P.state
  WHEN 'W' THEN ' WITH GRANT OPTION'
  ELSE ''
 END script
FROM
 sys.database_permissions P
  LEFT JOIN
 sys.all_objects O ON
  O.object_id = P.major_id
  LEFT JOIN
 sys.all_columns C ON
  C.object_id = P.major_id AND
  C.column_id = P.minor_id
WHERE
 NOT
 (
  P.class = 1 AND
  (
   EXISTS (SELECT TOP 1 1 FROM sys.extended_properties EP WHERE EP.major_id = O.object_id AND EP.name = 'microsoft_database_tools_support') OR
   O.is_ms_shipped = 1
  )
 )
ORDER BY
 1,2,3,4,5,6,7,8

2011-12-13

Free SQL database compare tool


Hi All, SqlDbAid now comes with a new database compare module.


The comparison uses an offline mode like the previous version,
but now you'll be able to visually analyze the differences.


All you have to do is connect to two databases, select the objects
you want to examine and then create two compare exports.


These files will be the input for the compare module.


2011-05-31

MKST Staging Area Template

MKST provides a standard approach to many of the common data warehousing activity tasks.


The data warehousing theory is out of scope here, but we could introduce some very basic concepts.


Most of the times we need to prepare data for a reporting/analysis tool without accessing directly the production databases:
- We don’t want to load the server interfering with every day user’s activities
- Normally source data is quite normalized (not ready for efficient reporting queries)
- We may need to do some cleansing and checks
- Many times we have to combine data from different sources (ERP, CRM…) and enrich them


MKST will enable you to transfer data from different sources (local or linked databases) to a data warehouse.


The MKST data warehouse flow consists of a process organized in three phases.
- Upload: transfer data from multiple sources to MKST staging
- Validation: check the uploaded data
- Import: transfer data from the staging area to a DWH


After configuring the staging the dbo.full_process stored procedure will do the job and the dbo.last_process_log will provide you the results.

MKST comes with four sql schemas.
- dbo: basic tables and procedures you should not modify
- ext: tables and procedure you may need to extend or add
- stg: tables that will serve as a destination for the original data
- dwh: views that will serve as a source for the DWH tables


You can download the script and the manual MKST.zip

2011-03-20

Update your statistics

If your database suffers of poor performances, before you try any optimization, you should update all statistics. Otherwise SQL Server will produce suboptimal plans.
Out of there a lot of databases have no maintenance plan simply because no one is in charge of it.


This simple query will generete the script for you.


SELECT 'UPDATE STATISTICS [' + SCHEMA_NAME(T.schema_id) + '].[' + T.name + ']'
FROM sys.tables T
WHERE NOT EXISTS
    ( SELECT TOP 1 1
     FROM sys.extended_properties EP
     WHERE EP.major_id = T.object_id
       AND EP.name = 'microsoft_database_tools_support' )
  AND T.is_ms_shipped = 0
ORDER BY 1

2010-09-11

Find Missing and Unused indexes with SqlDbAid

SQL Server's Dynamic Management Views (DMV) give you access to a lot of useful information.

The web is full of good scripts about SQL Server indexes, but if you prefer a friendly interface may be you could just use SqlDbAid application.

Lets create a test case.

Create and fill the following table

CREATE TABLE [dbo].[missing_idx]
(
[row_id] [bigint] NOT NULL,
[object_id] [int] NOT NULL,
[name] [nvarchar](128) NOT NULL,
[type] [char](2) NOT NULL,
CONSTRAINT [PK_missing_idx] PRIMARY KEY CLUSTERED ([row_id])
)

INSERT INTO dbo.missing_idx
SELECT TOP 20000 ROW_NUMBER() OVER (
                                    ORDER BY O.object_id) row_id,
                                                          O.object_id,
                                                          O.name,
                                                          O.type
FROM sys.all_objects O CROSS
JOIN sys.all_objects O1


then we could try to extract some data accessing the table using object_id column (no index there).

SELECT object_id,
       name,
       TYPE
FROM dbo.missing_idx
WHERE object_id IN
    (SELECT TOP 1 object_id
     FROM dbo.missing_idx)


Now run SqlDbAid, connect to your server and select the database where you created the table.

Select "Missing Indexes" from Tools --> Indexes menu.

You should see the following report:


Double click the row to get the create script:

CREATE NONCLUSTERED INDEX [IX_AT_dbo_missing_idx_1870364392] ON
[dbo].[missing_idx] ([object_id]) INCLUDE ([name], [type])



To find unused indexes from SqlDbAid select "Indexes Status" from Tools --> Indexes menu.

You should see the following report:


The read_vs_write column is the sum of all reads (scan, seek and lookup) minus the writes.
Look also to the date columns to know when was the last read.