Find what is going on with your SQL Server

Hello Guys, 

This blog is to tell you the way how you can find the things which are running in your SQL Server. Those things can be locks on your SQL Server, batch jobs, SQL queries, procedure etc. 

The way which i already know was executing procedure of either "SP_WHO" or new optimized with blockby column"SP_WHO2". 

Today i have learnt new way to do that from blog of SQL consultant who has optimized this process more in really broad way. Things which i have learnt from his blog , i am going to right them down below and i hope that will help you guys too.

-- Get the details whats going on in your SQL server

EXEC SP_WHO

-- It will give you few extra column from sp_who proc Which will help you to find which SPID is block by which SPID by having a quick watch in blockBy column

EXEC SP_WHO2

-- Here 55 is SPID which i took from sp_who or sp_who2 proc which is in blkby column

DBCC INPUTBUFFER(55)

 -- "SP_WHOISACTIVE" that you need to download from a blog by Adam (http://sqlblog.com/files/folders/beta/entry42453.aspx) and run the SQL script in your SQL server it will give you the statistics of your SQL Query

-- it will return the actual Query which are doing something with your SQL Server

-- you can also see the locks, waiting time, read and write time etc.

-- First you should start by executing "Sp_WHOISACTIVE" without any parameter. After when you feel comfortable with result then you should go a head with parameters.

-- I have learnt about two parameters till now which will give you the result with client statistics.

-- ** Note : if your server is really busy then try to optimize query first to get specific SPID which is causing issue according to you because in busy server there would be a lot of things going on and if you go with all then it will take long time to determine the things.

EXEC SP_WHOISACTIVE

         @get_plans=1,

           @get_transaction_info=1

View XML nodes in SQL Server as separate column

Hi, today i was asked by someone to view Xml file nodes in SQL Server as separated columns to check the consistency of data available in nodes.

I already knew that because i have done those kinds of stuff in beginning of my developer carrier.

Below Query will give you exact out but its little bit judicious because you have to give nodes name manually. I am working out to get the simple and easy solution when i will achieve my goal i will update this blog.

Demo xml data='<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

<Root source="C:\Data.csv" datetime="2015-05-12T16:16:55+06:00" >

     <record num="1">

          <ID>131383</ID>

          <EmpNum>51023</EmpNum>

          <Login>Rty</Login>

          <First_name>Amit</First_name>        

     </record>

    '

SELECT @XmlFile = BulkColumn

FROM  OPENROWSET(BULK 'path of xml', SINGLE_BLOB) x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlFile

SELECT ID, EmpNum, [Login],First_name

FROM OPENXML(@hDoc, 'Root/record')

WITH

(

ID varchar(50) '(ID)[1]',

EmpNum varchar(50) '(EmpNum)[1]',

[Login] varchar(100) '(Login)[1]',

First_name varchar(100) '(First_name)[1]'

)

EXEC sp_xml_removedocument @hDoc

GO

Search record among multiple columns of SQL table.

Today i have learnt a great thing related to searching a value among all columns of a SQL table. That is very useful even from this i can search a value in specific column of all table in SQL database.

Suppose you have a table this :

CREATE TABLE tblTest
(id INT IDENTITY(1,1),
columnA NVARCHAR(50),
coulmnB INT,
coulmnC NVARCHAR(250))-- Create a table 

INSERT INTO tblTest (columnA,coulmnB,coulmnC) VALUES
('Grim',2,'Master'),
('Kingdom',200,'Amar'),
('Paula',300,'Sara'),
('Kingdom',400,'Grim')--Inserting some Dummy Records

Now you have a requirement to search value 'Grim'  in a table for all column. In that case you will think to put where clause on each column of SQL table. but there is an alternative to do that. You can user below query to search a value in all columns of SQL table.

SELECT * FROM tblTest WHERE 'Grim' IN (columnA,coulmnB,coulmnC)

How to filter duplicate rows (duplicate records by column)

Today my old project manager ask me for a favor to give him a SQL query which exclude duplicate rows.

let me direct come to the point and show you some pictorial presentation.

--Query--
select data.provider_id, data.person_id, data.rank, data.RowNumber from
 (
 select provider_id, person_id
 , Rank()
 over (
 PARTITION BY provider_id
 ORDER BY provider_id ) AS [rank],
 ROW_NUMBER() OVER (
 PARTITION BY provider_id
 ORDER BY provider_id DESC ) as RowNumber
 from testtble

 ) as data
 where data.RowNumber <= 5
 order by provider_id

you can see that in row 2 and 3 provider and person id's are repeated.
To avoid the duplicacy i have to first find the duplicate rows based on columns records.
So i have created below query to remove that duplicacy from result.

--Query--
;WITH CTE AS(
SELECT *,(RowsNumber - Rank_Number + 1) AS Rank_Item FROM
    ( SELECT PROVIDER_ID ,
                PERSON_ID ,
                ROW_NUMBER() OVER( ORDER BY provider_id) AS RowsNumber ,
                RANK() OVER( ORDER BY provider_id) AS Rank_Number
           FROM(
        SELECT
  PROVIDER_ID ,
  PERSON_ID ,
  COUNT(*) AS CNT
                   FROM
  TESTTBLE
                   GROUP BY
  PROVIDER_ID ,
                            PERSON_ID
               )A
       )B
     )SELECT * FROM CTE WHERE Rank_Item<=5
      ORDER BY PROVIDER_ID;

   

Find an item from database according to char index

Hi ,

I have found the another way to find an item from SQL Server according to the position of character.
There are many ways to do it.

One way to do is use 'like' with '%' operator
Second way to do is  use 'like' with '_' operator
Third way to do is use charIndex function of SQL Server

Suppose you have find a record from database whose length should not greater then 12 and first char should be 'T' and last char should 'Z'.

In this case you cannot use like with %  because result will not depends upon length of parameter provided.

I would suggest to try this with your table and see the results you get. It will also give you a little learning.


SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T%Z'



SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T__________Z'

Get Updated Row Data

I never knew that such kind of things are there in SQL Server. Its amazing every person who works on SQL should know that. Its awesome.

 

Try those below queries with your table and columns 

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ID]

      ,[tblname]

      ,[UpdateTime]

  FROM [SQLToLinq].[dbo].[tblMNames]

  

  UPDATE 

                 tblMNames

  SET 

                 tblname='tblMnames'

  OUTPUT 

                INSERTED.UpdateTime , INSERTED.tblname

  WHERE 

                  tblname='tblMName'

SQL Server full text index

From full text index we can enhance the performance of SQL queries which you can directly see on SQL actual plan execution.

Documents supported for full text index :-

SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types

Language supported for full text :-

SELECT * FROM sys.fulltext_languages

ORDER BY lcid

Already existed full text catalog in Database :-

SELECT fulltext_catalog_id, name 

FROM sys.fulltext_catalogs

Table and Full Text Catalog relation :-

SELECT t.name AS TableName, c.name AS FTCatalogName

FROM sys.tables t JOIN sys.fulltext_indexes i

  ON t.object_id = i.object_id

JOIN sys.fulltext_catalogs c

  ON i.fulltext_catalog_id = c.fulltext_catalog_id

Creation of Full text Index on table column :-

--!!! Drop Full Text Catalogue !!!--
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
ALTER FULLTEXT INDEX ON [dbo].[tblData] DISABLE
GO

/****** Object:  FullTextIndex     Script Date: 06/13/2013 23:11:07 ******/
IF  EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[tblData]'))
DROP FULLTEXT INDEX ON [dbo].[tblData]
GO
-------------------------------------------------------------------------------------------------------------------
--!!! Add a new column !!!--
IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
  WHERE TABLE_NAME = 'tblData'
  AND COLUMN_NAME = 'Records') 
BEGIN
ALTER TABLE dbo.tblData ADD
Records nvarchar(450) NULL
END
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Update the Records column with Values !!!---
UPDATE dbo.tblData SET [Records]=FirstName +' | ' LastName
GO
-------------------------------------------------------------------------------------------------------------------

---!!! Add index on DisplayUserText Column !!!---
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_Users_Records' AND object_id = OBJECT_ID('tblData'))
BEGIN
CREATE NONCLUSTERED INDEX [IX_Users_Records] ON [dbo].[tblData] 
(
[Records] ASC
)
WHERE ([Records] IS NOT NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create full text catalog !!!---
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = 'FTC_tblData')
BEGIN
CREATE FULLTEXT CATALOG [FTC_tblData] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
END
GO
------------------------------------------------------------------------------------------------------------------
---!!! Create Stoplist !!!---
IF NOT EXISTS (SELECT Name FROM sys.fulltext_stoplists WHERE [name] = 'FTS_tblData')
CREATE FULLTEXT STOPLIST FTS_tblData FROM SYSTEM STOPLIST;
GO
------------------------------------------------------------------------------------------------------------------
--!!! Create Full text index !!!--
IF NOT EXISTS (SELECT * FROM sys.fulltext_indexes WHERE object_id = object_id('tblData'))
BEGIN
 CREATE FULLTEXT INDEX ON DBO.tblData([FirstName] LANGUAGE 0, [LastName] LANGUAGE 0)
 KEY INDEX PK_tblData ON FTC_tblData
    WITH STOPLIST = [FTS_tblData];
END
GO

---!!! Now we are done with full text index !!!---
(For more details visit this URL : https://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/)

How to use :-

SELECT *
FROM tblUsers
WHERE CONTAINS(FirstName,'"*Jes*"');

Performance tuning of SQL query by correcting indexes

-- Missing Index Script-- Original Author: Pinal Dave (C) 2011SELECT TOP 25
dm_mid.database_id dm_mid.database_id dm_mid.database_id AS DatabaseID,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,dm_migs.last_user_seek AS Last_User_Seek,OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +CASEWHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'ELSE ''END+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ + + ']'+ ' ON ' + dm_mid.statement+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ + + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE'' END+ ISNULL (dm_mid.inequality_columns, '')
+ + + ')'+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_StatementFROM sys.dm_db_missing_index_groups dm_migINNER JOIN sys.dm_db_missing_index_group_stats dm_migsON dm_migs.group_handle = dm_mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details dm_midON dm_mig.index_handle = dm_mid.index_handleWHERE dm_mid.database_ID = DB_ID()ORDER BY Avg_Estimated_Impact DESC

GO




-- Unused Index Script
-- Original Author: Pinal Dave (C) 2011SELECT TOP 25
o.name AS ObjectName, i.name AS IndexName, i.index_id AS IndexID, dm_ius.user_seeks AS UserSeek, dm_ius.user_scans AS UserScans, dm_ius.user_lookups AS UserLookups, dm_ius.user_updates AS UserUpdates, p.TableRows, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' +QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'FROM sys.dm_db_index_usage_stats dm_iusINNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id ANDdm_ius.OBJECT_ID = i.OBJECT_IDINNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_IDINNER JOIN sys.schemas s ON o.schema_id = s.schema_idINNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_IDFROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) pON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_IDWHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'AND i.is_primary_key = 0AND i.is_unique_constraint = 0ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups)ASCGO


-- Find Unused indexes
USE AdventureWorks
GODECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),INDEXNAME = I.NAME,I.INDEX_IDFROM SYS.INDEXES IJOIN SYS.OBJECTS OON I.OBJECT_ID = O.OBJECT_IDWHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1AND I.INDEX_ID NOT IN (SELECT S.INDEX_IDFROM SYS.DM_DB_INDEX_USAGE_STATS SWHERE S.OBJECT_ID = I.OBJECT_IDAND I.INDEX_ID = S.INDEX_IDAND DATABASE_ID = @dbid)ORDER BY OBJECTNAME,I.INDEX_ID,INDEXNAME ASCGO