SQL Server and MS BI With a Geek Touch

Query to track all the indexes with fragmentation in a database.

SELECT 

OBJECT_NAME(object_id) AS TablelName

,(SELECT name FROM sys.indexes WHERE object_id = a.object_id and index_id = a.index_id) IndexName

,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(‘<<DatabaseName>>’), NULL, NULL, NULL, NULL) a

WHERE avg_fragmentation_in_percent > 40

AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’)

ORDER BY avg_fragmentation_in_percent DESC

The <<DatabaseName>> needs to be filled with the database of your choice.

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

The GUID (uniqueidentifier) data type is one of the most interesting data types available. uniqueidentifier column holds a GUI, a string of 32 random hexa-decimal characters (0-9, A-F) in blocks separated by hyphens.  A GUID will look like

5166AA1D-A18F-4D3C-A7CA-3F3CBF8CEE2B 

EDCF4B8A-4154-449B-972A-2224F8450BF1

CEA4C383-51FD-4B99-A67D-F5C34D5F1013     

5D9FDC84-632D-4450-B183-04CBBEE87DC6

GUIDs have both Pros and cons. GUIDs are pretty big in size and require 16 bytes of storage. This usually makes it slow during joins of large dataset.

Now you might think, What is so great about GUID? Although it occupies lot of space there are several advantages. First, a GUID will be a completely unique value and no other GUID in the world will share the same string. This means that you can use GUIDs as PKs on your tables if you will be moving data between databases. This technique prevents duplicate PKs when you actually copy data. GUIDs are widely used in Microsoft products to identify interfaces, replica sets, records, and other objects.

In Sql Server, you can create a new GUID by using the function NEWID(). This function is a non-deterministic function. It generates a new value every time you execute function .

Select NEWID()

 the datatype is named in SQL as “uniqueidentifier”

Many have asked me this in the past. so what exactly is the difference between different type of joins? lets consider case by case, this will give you a better understanding. let us consider two tables for this.

1. Inner Join ( also commonly called join) Using INNER JOIN tells database to return only the columns that match the condition provided in the join clause. now if we want to select the employees and their respective team names, all we need to do is to join these two tables.INNER JOIN is often (but not always) created between the primary key column in one table and the foreign key column of another .

Read more…

The first thing that goes through your mind might be ” why cant we SP_SPACEUSED instead ? “. You are right. However , it will result in a row by row operation rather than a set operation. This will be a major headache in the usual production environment with large number of tabls and huge data. The below query can give you the result in few seconds. The step 2 can be avoided by careful coding . I have included update statement for easier understanding of the code. 

SELECT OBJECT_ID

 , SUM (RESERVED_PAGE_COUNT) RESERVEDPAGES

 , SUM (USED_PAGE_COUNT) USEDPAGES

 , SUM ( CASE WHEN (INDEX_ID < 2) THEN (IN_ROW_DATA_PAGE_COUNT + LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT) ELSE LOB_USED_PAGE_COUNT + ROW_OVERFLOW_USED_PAGE_COUNT END )PAGES, SUM ( CASE WHEN (INDEX_ID < 2) THEN ROW_COUNT ELSE 0 END ) AS [ROWCOUNT]  

 INTO   #SIZE  

 FROM  SYS.DM_DB_PARTITION_STATS 

 GROUP BY OBJECT_ID     

UPDATE #SIZE  
SET RESERVEDPAGES= (RESERVEDPAGES + RESERVED_PAGE_COUNT)

 ,USEDPAGES = (USEDPAGES + USED_PAGE_COUNT)  

 FROM

#SIZE T

 ,SYS.DM_DB_PARTITION_STATS P

 ,SYS.INTERNAL_TABLES IT  

WHERE T.OBJECT_ID=IT.PARENT_ID AND IT.INTERNAL_TYPE IN (202,204) AND P.OBJECT_ID = IT.OBJECT_ID   

 

 

SELECT  DATASIZEINKB= SUM(PAGES) * 8 , INDEXSIZEINKB = SUM((CASE WHEN USEDPAGES > PAGES THEN (USEDPAGES - PAGES) ELSE 0 END)) * 8, UNUSEDSIZEINKB = SUM((CASE WHEN RESERVEDPAGES > USEDPAGES THEN (RESERVEDPAGES - USEDPAGES) ELSE 0 END)) * 8

FROM #SIZE  

 

DROP TABLE #SIZE 

 

 pardon me for the allignments, as i could not get it right .

 

Usually we capture the SQl/plan handles for the query stats or from cached plan DMVs and supply these as the inputs to the DMFs to translate everything into human-readable format, there is a better / easier way to arrive at this translation. The queries below shows the approach that can serve as an alternative.

SELECT *
FROM 
        sys.dm_exec_cached_plans 
CROSS APPLY 
      sys.dm_exec_query_plan(plan_handle)

SELECT *
FROM 
        sys.dm_exec_query_stats 
CROSS APPLY 
       sys.dm_exec_sql_text(sql_handle)

SELECT *
FROM 
        sys.dm_exec_query_stats 
CROSS APPLY 
       sys.dm_exec_query_plan(plan_handle)

The CROSS APPLY operator invokes a table-valued function for each row within a table.  and As usual, do not forget to add the WHERE clause to restrict the number of records.

Take this as a continuation of the post for identifying columns on which indexes are missing

The most difficult decision to make is which of the indexes proposed by the sys.dm_db_missing_index can provide the most benefit. Applying some basic calculations/ numerical comparison based on the records retrieved can help you acheive this task. The following examples shows a sample code you can use :

SELECT USER_SEEKS * AVG_TOTAL_USER_COST * (AVG_USER_IMPACT * 0.01) AS IMPACT,*
FROM
 SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS GRPSTATS  WITH(NOLOCK)
INNER JOIN 
SYS.DM_DB_MISSING_INDEX_GROUPS  AS GROUPS WITH(NOLOCK)
ON GRPSTATS.GROUP_HANDLE = GROUPS.INDEX_GROUP_HANDLE
INNER JOIN 
SYS.DM_DB_MISSING_INDEX_DETAILS  AS  DETAILS WITH(NOLOCK)
ON GROUPS.INDEX_HANDLE = DETAILS.INDEX_HANDLE
ORDER BY 1

On operational systems, values above 5,000 indicate indexes that should be evaluated for creation.When the value passes 10,000, you generally have an index that can provide a significant performance improvement for read operations.

This code is only for read activity,you may have to redesign it for other maintenance operations as well.

Most often identifying the columns for which the indexes needs to be created is a difficult job. Often we land up creating indexes on columns that may not be used very often. Even worse is not creating indexes on the columns that may be used very often. sometimes it becomes difficult to identify the queries that run the most and the columns on which it runs. I normally use a query as below to get the list of columns for which indexes are missing.

SELECT DET.*, ST.*
FROM    SYS.DM_DB_MISSING_INDEX_GROUP_STATS ST
INNER JOIN
SYS.DM_DB_MISSING_INDEX_GROUPS GRP
ON GRP.INDEX_GROUP_HANDLE = ST.GROUP_HANDLE
INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS DET  ON DET.INDEX_HANDLE = GRP.INDEX_HANDLE
WHERE DATABASE_ID = DB_ID(‘MYDB’)

You can use the columns mentioned in “Equality_columns, included_columns” to build the indexes. I advice you to go with the values in “AVG_USER_IMPACT ,USER_SEEKS, AVG_TOTAL_USER_COST” to take the decision on whether to create index or not on a particular column. It is also not advisable to create index on each and every column.

Hope this was of some help to you. Let me know if the post was useful or not and also if you have something similar .

There are times when you need to insert some special characters such as “newline”,”tab” etc as values into a field. Usually every one tries with ‘\n’ or ‘\t’, in vain. we can do this by using the ASCII value of the character. The steps are very simple. if you want to insert a special character find the ASCII value of that character like ‘ 9 for tab’, ‘ 12 for new line’ etc. you can find the lists of characters with its ASCII values at http://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table. Once this is done, concatenate your string with the character of that particular ASCII value as

‘my string and’+ char(12)+’someting’

this will be

my string and

something

an examle is as shown below

INSERT INTO TABLES( FIELDS)

SELECT ‘hello’+char(9)+’:'+char(12)+’this is a new line’

this will be stored in the table as

hello    :

this is a new line

in the above example I inserted a tab after hello and a new line after ‘:’

you can similarly use

new line = char(12)

space = char(32)

horizontal tab = char(9)

carriage return = char(15)

vertical tab = char(13)

end of text = char(3)

for more look at the ASCII Table mentioned above.

This is a commaon question that comes to each developers mind.  Most of them considers IN and EXISTS as same but with different syntax. This aint true .

We can use both operators to fetch the same results. Let me take few examples before we go to the actuals.

SELECT * FROM TABLE_1 returns records as shown below

SELECT * FROM TABLE_2 returns

if we want to get the data in TABLE_1 which are present in “Field3″ of TABLE_2 then we can use the query with IN operator as

SELECT * FROM TABLE_1
WHERE FIELD1 IN ( SELECT FIELD3 FROM TABLE_2)

We can also use the query with EXISTS as

SELECT * FROM TABLE_1
WHERE EXISTS(    SELECT ‘X’
FROM TABLE_2
WHERE TABLE_1.FIELD1 = TABLE_2.FIELD3
)

when using EXISTS always use the where clause in the subquery to join the tables. Not doing so will result in fetching all the records from the main table. for eample if we consider the query below

SELECT * FROM TABLE_1
WHERE EXISTS(    SELECT Field3
FROM TABLE_2

)

will fetch all the records from TABLE_1 and is same as the query

SELECT * FROM TABLE_1

The other difference is in performance( depending on which table is selected in outer/ inner query). EXISTS works faster than IN. you can check the performance plans of the above query for more info.