SQL Server Create Index – sys.dm_exec_requests Percent Completed

I had an observation today in regards to creating an index that you may find interesting.  Per SQL Server 2008 (not R2) documentation for sys.dm_exec_requests it mentions that percent_completed works for the following commands:

  • ALTER INDEX REORGANIZE
  • AUTO_SHRINK option with ALTER DATABASE
  • BACKUP DATABASE
  • CREATE INDEX
  • DBCC CHECKDB
  • DBCC CHECKFILEGROUP
  • DBCC CHECKTABLE
  • DBCC INDEXDEFRAG
  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • KILL (Transact-SQL)
  • RESTORE DATABASE,
  • UPDATE STATISTICS.

sys.dm_exec_requests (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177648(v=sql.100).aspx

I was creating an index and I wanted to know how much time was remaining until the index creation finished.

So I executed the following query. I constantly ran this query, but percent_complete never changed from 0.

This seemed odd. Based on SQL Server documentation percent_completed should have been provided for Create Index.

Here’s an individual that talks about the discrepancies for sys.dm_exec_requests
Differences in documentation for sys.dm_exec_requests
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/07/differences-in-documentation-for-sys-dm-exec-requests.aspx

SQL Server documentation for 2008 R2 and 2012 does not include Create Index for percent_complete.

The conclusion here is that for sys.dm_exec_requests, percent_complete is not provided for Create Index.  And I’m not sure if it ever was supported.

Other links
DOC : sys.dm_exec_requests topic is inconsistent between versions
http://connect.microsoft.com/SQLServer/feedback/details/641790/doc-sys-dm-exec-requests-topic-is-inconsistent-between-versions

SQL Server Objects In Memory VS Disk

For a few months I’ve been meaning to do an extensive post on a SQL Server query that would show the amount of each index/table in memory (Data Cache). I haven’t had the time to do this write-up, so I’m jut providing the query at this time. Hopefully in the future I will have time to describe this better.

The following query has been extremely beneficial at my work. It has help to identify indexes that should not be in memory and performance issues. This query doesn’t usually identify a root-cause, but it does seem to point you into the correct direction.

In the past there was a situation where a Cluster Index Scan was being used on a 20 GB table. This caused major performance problems for our system. The Cluster Index was loaded into memory (data cache) which forced other important data out of the data cache. By using this query to show indexes in memory, it was easy to identify the table that was being queried and inturn find the query that was causing the problem.

IF OBJECT_ID('tempdb..#PhysicalBufferSize') IS NOT NULL
BEGIN
PRINT 'DROP TABLE tempdb..#PhysicalBufferSize'
DROP TABLE #PhysicalBufferSize
END

SELECT
PhysicalSize.TableName
,PhysicalSize.IndexName
,PhysicalSize.Index_MB
,BufferSize.Buffer_MB
,CASE
WHEN Index_MB != 0 AND Buffer_MB != 0 THEN
CAST(Buffer_MB AS Float) / CAST(Index_MB AS Float)
ELSE 0
END IndexInBuffer_Percent
INTO #PhysicalBufferSize
FROM
(
--Index Disk Allocation per file
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
SUM(a.used_pages) / 128 AS 'Index_MB'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON
p.OBJECT_ID = i.OBJECT_ID
AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON
a.container_id = p.partition_id
WHERE
i.object_id > 100
GROUP BY i.OBJECT_ID,i.index_id,i.name
--ORDER BY 8 * SUM(a.used_pages) DESC--OBJECT_NAME(i.OBJECT_ID),i.index_id
) PhysicalSize

LEFT JOIN
(
--Index Memory Allocations per file
SELECT
obj.[name] TableName,
i.[name] IndexName,
obj.[index_id] IndexID,
i.[type_desc],
count_BIG(*)AS Buffered_Page_Count ,
count_BIG(*) /128 as Buffer_MB --8192 / (1024 * 1024)
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON
au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3 OR au.type = 2)
) AS obj ON
bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on
i.object_id = obj.object_id
AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
--ORDER BY Buffered_Page_Count DESC
) BufferSize ON
PhysicalSize.TableName = BufferSize.TableName
AND PhysicalSize.IndexID = BufferSize.IndexID
ORDER BY Buffer_MB DESC

--*****************************************************************
-- Queries that work of Temp Table
--*****************************************************************
SELECT *
FROM #PhysicalBufferSize
ORDER By Buffer_MB DESC--TableName, IndexName DESC

SELECT TableName, SUM(Index_MB) Index_MB, SUM(Buffer_MB) Buffer_MB,
CASE
WHEN SUM(Index_MB) != 0 AND SUM(Buffer_MB) != 0 THEN
CAST(SUM(Buffer_MB) AS Float) / CAST(SUM(Index_MB) AS Float)
ELSE 0
END IndexInBuffer_Percent
FROM #PhysicalBufferSize
GROUP BY TableName
ORDER BY Buffer_MB DESC

SELECT SUM(Index_MB) Index_MB, SUM(Buffer_MB) Buffer_MB,
CASE
WHEN SUM(Index_MB) != 0 AND SUM(Buffer_MB) != 0 THEN
CAST(SUM(Buffer_MB) AS Float) / CAST(SUM(Index_MB) AS Float)
ELSE 0
END IndexInBuffer_Percent
FROM #PhysicalBufferSize

SELECT *
FROM #PhysicalBufferSize
ORDER By Index_MB DESC

The following grid shows an example output from AdventureWorks database. The grid identifes the size of the index on disk (Index MB) and the amount of the index in the Data Cache.

Table
Name
Index Name Index MB Buffer MB Percent
Person PK_Person_BusinessEntityID 29 29 1.00
SalesOrderDetail PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 9 9 1.00
TransactionHistory PK_TransactionHistory_TransactionID 6 6 1.00
DatabaseLog NULL 6 6 1.00
WorkOrderRouting PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence 5 5 1.00
SalesOrderHeader PK_SalesOrderHeader_SalesOrderID 5 5 1.00
WorkOrder PK_WorkOrder_WorkOrderID 4 4 1.00
TransactionHistoryArchive PK_TransactionHistoryArchive_TransactionID 5 4 0.80
Address PK_Address_AddressID 2 2 1.00
CreditCard PK_CreditCard_CreditCardID 1 1 1.00
EmailAddress PK_EmailAddress_BusinessEntityID_EmailAddressID 1 1 1.00
Password PK_Password_BusinessEntityID 1 1 1.00
PersonPhone PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID 1 1 1.00
Address IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode 1 1 1.00
Shift AK_Shift_StartTime_EndTime 0 0 0.00

Resource:
SQLTEAM - What Data is in SQL Server's Memory?
MSDN - sys.dm_os_buffer_descriptors (Transact-SQL)
Glenn Berry - SQL Server Performance 1
Glenn Berry - SQL Server Performance 2

Change Internet Explorer default editor for View Source

I’m currently running IE 8 on a windows 7 machine. When I select “View Source” from the browser, Notepad is open. In IE 8 this editor is lot better then in previous version.   But I like NotePad++ as my default editor. In previous versions of IE I had to edit the registry to make NotePad++ the default view. In IE 8 the registry changes do not seem to work. 

To enable NotePad++, or any editor, to be the default for View Source, you must open Developer Tools.  The Developer tools can be open by the F12 key or the Tools menu (see image blow).

image

Once you have Developer Tools open, select “File” –> “Customize Internet Explorer View Source”.  Here you can select the Default Viewer, Notepad or other.

image

If you select other, a dialog will be presented for you to select the application you want as the for View Source

image

Error when opening Visual Studio 2008 project in Visual Studio 2010

The error I receive when trying to open Visual Studio 2008 project/solution in Visual Studio 2010 is “cannot be opened because its project type (.csproj) is not supported by this version of the application.” and “To open it, please use a version that supports this type of project”

image

I found a few link to this issue but they all refer to opening a Visual Studio 2005 project in 2008.  Since I couldn’t find an answer pertaining to VS 2010, I decided to use the solutions that I found for 2008. 

All the solution I found mention running this command “devenv.exe /resetskippkgs”.  So I open the Command Prompt and navigated to “C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDE” and ran “devenv.exe /resetskippkgs”.  This seem to fix the issue.  Visual Studio 2010 started and I receive the usual notification when opening a 2008 project in 2010.

image

I still have problems in all addition attempts to load 2008 project in Visual Studio 2010, I still need to go through this same process.

Resources

Default Enable Quick Edit for Windows Command Prompt (Console)

It seems that I’ve used Windows and the Command Prompt (Console) for ever and I never new of this feature.  Every time I wanted to copy or paste from the Command Prompt I had to right click and select “mark” or “paste” in the the window or click and bring up the menu.  It’s not that big of a deal, but saving 2 clicks or keystrokes adds up in 10 years.  I’ve must have wasted at least 30 minutes.   Here are my steps to set Quick Edit Mode as the default for the Command Prompt.

I know the following steps work for Windows XP and Windows 7.

The following screen show how to change the options for Command Prompt that is currently open.  When you close the Command Prompt this option is not saved, so the next time you open the Command Prompt this option will need to be reset.

image

The following image shows how to change the default configuration so that Quick Edit Mode is enabled by default.

image

Resources:
TechNet – QuickEdit –http://technet.microsoft.com/en-us/library/cc978582.aspx

Entity Framework

 
Resources:
 ADO.NET Entity Framework
 
Introducing ADO.NET Entity Framework(By Julia Lerman) 
 
MSDN Quickstart (Entity Framework)
 
ADO.NET Entity Framework
 
Additional MSDN Links
The following topics enable you to learn more about the Entity Framework:
          Additional Entity Framework Resources

Provides links to conceptual topics and links to external topics and resources for building Entity Framework applications.

          Getting Started (Entity Framework)

Provide information about how to get up and running quickly using the Quickstart (Entity Framework), which shows how to create a simple Entity Framework application.

          Quickstart (Entity Framework)

Shows you how to use the Entity Data Model tools with Visual Studio 2008 to quickly create your first Entity Framework application.

          Application Scenarios (Entity Framework)

Provides task-based links to topics that match specific application scenarios, such as writing queries, binding objects to data controls, or implementing business logic.

          Entity Framework Features

Provides more detailed information on the features that compose the Entity Framework and links to topics that discuss those features.

          Entity Framework Terminology

Defines many of the terms that are introduced by the EDM and the Entity Framework and that are used in Entity Framework documentation.

          Tons of “How Do I?” Videos — Data Platform Development
 
Why use the Entity Framework
A decent blog that discusses LINQ to SQL, Tradiontal ADO.NET, and NHibernate.
 
Entity Framework Q&A
EntityClient + Entity SQL
Object Services + Entity SQL
Object Services + LINQ
 
Introducing the Entity Framework (by Shawn Wildermuth)
 
Julie Lermans Blog( author of Entity Framework)
 
 
MSDN Webcast: MSDN geekSpeak: Julie Lerman on ADO.NET (Level 200)
Julie does a great job of presenting EF.  She has a book called Programming Entity Framework, which is an O’Reilly press book, that is schedule to be released January 15, 2009.
 
dnrTV – Dan Simmons on The Entity Framework Part 1
 
dnrTV – Dan Simmons on The Entity Framework Part 2
 
LINQ to Relational Data: Who’s Who?
LINQ to SQL; LINQ to Entities
 
Entity Framework- The Crib Sheet
 
ADO.NET Entity Framework Essential Resources  October 2008
 
 

DOS – Batch Join SQL Files

We have all of our SQL script in source control.   Whenever I update the database I have to go through each file and run it individually.  I wanted a way to be able to run all SQL scripts.  So, I crated a DOS batch that would join all the files in a directory and add "GO" and CRLF between each script.
 
FOR %%A IN (*.SQL) DO (
 TYPE %%A >> AllSql.txt
 echo. >> AllSql.txt
 echo   GO >> AllSql.txt
 echo. >> AllSql.txt
 echo –%%A >> AllSql.txt
)
 
I was using Copy but I was getting NULLs in my resulting script, very strang.  I change the Copy to TYPE and everything seemed to work correctly
Also, echo. does CRLF
 
Resource
How to insert a carriage return with batch
 
How to write a DOS batch file to loop through files
 
 
 

Cool Tools and Tips

Every time I rebuild my computer I can never remember the tools that I installed and the custom changes i make to the environemnt.  Also I added a few links to others who have similar lists.
 
 
 
 
David Yardy.

SQL Server For Each Procedure

I created my sp_ForEachProcedure base on Microsoft undocumented sp_msForEachTable.  It doesn’t have all the features of sp_msForEachTable, but does include a few of them.  I even added a few custom parameters (@print, @execute).   I did dynamic SQL because I was having problems creating the where statement.  I believe I could use a case, but went ahead and used the dynamic sql.  I would like to join to syscomments and search the actual stored procedure, but ran out of time. 
 
In the future I plan on creating sp_ForEachView
 
 
 

–example of sp_msforeachtable

–exec sp_msforeachtable @command1=’PRINT ”?”’, @whereand=’ and o.name like ”%Human%”’

 
ALTER

PROCEDURE sp_ForEachProcedure

  @command1

VARCHAR(MAX),

  @whereand

VARCHAR(MAX) = NULL,

  @print

BIT = 1,

  @execute

BIT = 0

AS

–DECLARE @command1 VARCHAR(MAX)
–DECLARE @whereand VARCHAR(MAX)
–SET @command1 = ‘PRINT ”?”’
–SET @whereand = ‘name like ”%contact%”’
 

DECLARE

@executeCommand NVARCHAR(MAX)

SET

@executeCommand =

  ‘ DECLARE @statementTable TABLE (ID INT IDENTITY(1,1) ,statement NVARCHAR(MAX)) ‘

+

  ‘ DECLARE @userCommand NVARCHAR(MAX) ‘

+

  ‘ INSERT INTO @statementTable (statement) ‘

+

  ‘ SELECT ‘

+

  ‘ REPLACE(”’

+ REPLACE(@command1, ””, ”””) + ”’, ”?”, ”[” + p.name + ”]”)’ + ‘ AS Statement’ +

  ‘ FROM SYS.Procedures p ‘

IF

(@whereand IS NOT NULL)

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ WHERE ‘

+ REPLACE(@whereand, , ””)

END

IF

(@print = 1)

BEGIN

  SET @executeCommand = @executeCommand + ‘ SELECT * FROM @statementTable’

END
IF

@execute = 1

BEGIN

  SET @executeCommand = @executeCommand +

  ‘ DECLARE @loopCount INT ‘ +

  ‘ DECLARE @statement NVARCHAR(MAX) ‘ +

  ‘ SELECT @loopCount = COUNT(*)FROM @statementTable ‘ +

  ‘ WHILE @loopCount <> 0 ‘ +

  ‘ BEGIN ‘ +

  ‘ SET @statement = (SELECT statement FROM @statementTable WHERE ID = @loopCount) ‘ +

  –‘ PRINT ”EXECUTING: ” + @Statement ‘ +

  ‘ EXEC sp_executesql @Statement ‘ +

  ‘ SET @loopCount = @loopCount – 1 ‘ +

  ‘ END ‘

END

–FOR DEBUGGING
–PRINT @executeCommand

EXEC

sp_executesql @executeCommand

GO
 

–sp_ForEachProcedure ‘PRINT ”?”’, ‘NAME LIKE ”%Human%”’, 1
–sp_ForEachProcedure ‘GRANT EXECUTE ON ? TO Scrub ‘, ‘NAME LIKE ”%Human%”’, 1, 1
 
References:
Is a Temporary Table Really Necessary (How not to use cursors):