forcing recompilation

Ways to force stored procedure recompilation:

sp_recompile
Eexecute with recompile
Ccreate procedure with recompile
Remove plan from cache: dbcc freeproccache

Create temporary table inside stored procedure

enforce automatic proceure recompilation

mixing DDL and DML will make procedure to be recompiled each time it meets the first DML after DDL. A common use of this would be creating temporary table in begining of procedure. But remember to put all creation statements in one place so that recompilation happens only one time. if DDL and DML are mixed several times, the procedure will be recompiled for several times.

other situations that make procedure recompiled include
- table structure change
- index change, even adding irelevant index on relafed tables
- number of rows being changed 3xceeds the threshold

NoLock was blocked

For nolock in SQL Server, it's equivalent to READUNCOMMITTED transaction isolation level. It will not be blocked by other locks event exclusive locks but SCH-M (schema modification lock) because it does put S lock on DB and SCH-S(stability) lock on table.

Today, when I issued a query like "select * from test nolock", it was blocked by an update statement from another connection. I was so confused and began to doubt if my knowledge is solid. :(
Then after a while I realized that for nolock table hint, it does not require WITH keyword, but it needs to be enclosed in brackets\parentheses. In the experimented statement, the nolock was treated as table alias. after correcting it to be "select * from test (nolock)", it executed as expected.

A subtle mistake to me today.

Database file system, a practice

A record of my experiment. I will make it more complete later.

1. table design.
A file system entity is either a file or a directory. a file belongs to a directory. root directory belongs to itself.
1.1 ERD

1.2 Creation and Initialization
CREATE TABLE [dbo].[DBFile](
    [ID] [bigint] identity(0,1) NOT NULL,
    [Name] [varchar](250) NOT NULL,
    [Type] [smallint] NOT NULL,
    [Parent] [bigint] NULL,
    [Content] [varbinary](max) NULL,
 CONSTRAINT [PK_DBFile] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[DBFile]  WITH CHECK ADD  CONSTRAINT [FK_DBFile_DBFile] FOREIGN KEY([Parent])
REFERENCES [dbo].[DBFile] ([ID])
GO

ALTER TABLE [dbo].[DBFile] CHECK CONSTRAINT [FK_DBFile_DBFile]
GO

--initiate the root record
set IDENTITY_INSERT dbfile on
insert into dbfile (id,[Name],parent,type) values(0,'/',0,0)
set IDENTITY_INSERT dbfile off

2. interfaces and implementation
2.1create new file
create procedure usp_DBFile_Ins @parent bigint=0, @fileName varchar(200), @isDirectory bit=0, @fileId bigint output
as
begin
insert into dbfile ([Name],parent,type) values(@fileName,@parent,case when @isDirectory=0 then 1 else 0 end)
set @fileId= @@identity
end
2.2 update file's content
create procedure usp_DBFile_upd @fileId bigint, @content varbinary(max)
as
begin
update DBFile set Content=@content where ID=@fileId
if @@ROWCOUNT=0
begin
    RAISERROR ('Specified file ID is not existing.',1,1)
    return -1
end
return 0

end
2.3 type file content
create procedure usp_TypeFile @fileId bigint
as
begin
select ID fileId,name fileName,convert(varchar(max),content) from DBFile where ID=@fileId and [TYPE] = 1
if @@ROWCOUNT<1 br="br">    RAISERROR ('Specified ID is not a file.',1,1)
end

2.4 list directory/file

create procedure usp_ListDir @fileID bigint
as
begin
WITH dirFiles
as
(
--first level directory or file
select ID,name,case when [TYPE]=1 then 'File' else 'Directory' end FileType from DBFile where ID=@fileID
union all
select af.ID,af.name,case when af.[TYPE]=1 then 'File' else 'Directory' end FileType
from DBFile af join dirFiles df on af.Parent=df.ID
where af.ID>0
)
select * from dirFiles
end

2.5 Delete file

3. test codes

exec usp_DBFile_Ins 0,'C:',1
exec usp_DBFile_Ins 0,'D:',1
exec usp_DBFile_Ins 1,'testFile.txt',0

select * from dbfile

declare @content varbinary(100)
set @content=convert(varbinary(100),'test content')
exec usp_DBFile_upd 3,@content
exec usp_TypeFile 3

select convert(varchar(100),convert(varbinary(max),'test content'))

exec usp_ListDir 0

declare @fileId bigint
declare @content varbinary(100)
set @content=convert(varbinary(100),'test content. this file is on drive d.')
exec usp_DBFile_Ins 2,'testFileOnD.txt',0,@fileId output
select @fileId
exec usp_DBFile_upd @fileId,@content
exec usp_TypeFile 7
exec usp_ListDir 2

Monitor General Health of SQL Server Database

1.Check the slow queries

The reason why it's slow might be because of blocked by other processes. But it's a good start place for those running slow.

select getdate() runtime,st.text,qs.plan_handle,qs.last_execution_time,qs.last_elapsed_time/1000000.00/60 Duration_minutes from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(QS.sql_handle) as ST order by last_elapsed_time desc

Take  a look at the slowest queries and try to optimize them. Their execution plan can be retrieved from sys.dm_exec_query_plan ( plan_handle ). use cross apply to add query plan into the result.
Or just pick up on and examine individually.
 
- get the plan_handle of the query whose plan is going to be examined.
- retrieve the plan
  select * from  sys.dm_exec_query_plan ( plan_handle )
- click on the link to get graphic execution plan for easy reading.

Note:
For an execution of procedure, each statement will end up one entry in the sys.dm_exec_query_stats.
the query can be changed to work on the query plan for that proc. then you can focus on the one that uses most of the time to execute.

For example, Change the above query further to work on one specific query plan.

select st.text,qs.plan_handle,qs.last_execution_time,qs.last_elapsed_time/1000000.00/60 Duration_minutes
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(QS.sql_handle) as ST
where qs.plan_handle=0x05000900C0DF911440A3FAAF000000000000000000000000
order by last_execution_time asc,last_elapsed_time desc

Checking its execution plan, you will see query 18 is the most costly one, that is also corresponding to the execution duration in the query statistics. Then you will be sure that is the performance bottleneck in that procedure. The rest of the work is to focus on it and tune it.

2. Find idle sessions that have open transactions.
An idle session is one that has no request currently running.

SELECT s.* FROM sys.dm_exec_sessions
AS s
WHERE
--has open transaction
EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id )
--no request
AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id ); 

3. Check the index usages

3.1 check how the indexes are used.

select db_name(iu.database_id)DBName,OBJECT_NAME(iu.object_id) ObjectName,i.name IndexName,iu.*
from sys.dm_db_index_usage_stats iu join sys.indexes i on iu.object_id=i.object_id and iu.index_id=i.index_id
join sys.objects o on iu.object_id=o.object_id
where database_id=9 and o.type='U'
...

3.2 Check  size and fragmentation information: sys.dm_db_index_physical_stats
--the following statement check the index on object 1464392286 in database with id 9.
select * from sys.dm_db_index_physical_stats(9,1464392286,null,null,null)
  
Reducing Fragmentation in an Index(http://msdn.microsoft.com/en-us/library/ms188917.aspx)

When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

• Drop and re-create the clustered index.
  Re-creating a clustered index redistributes the data and results in full data pages. The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. If the index creation is interrupted, the index is not re-created. For more information, see CREATE INDEX (Transact-SQL).
  
• Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Because this is an online operation, the index is available while the statement is running. The operation can also be interrupted without losing work already completed. The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.
  
• Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. For more information, see ALTER INDEX (Transact-SQL).

Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. This causes slower response times. If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.

Reducing Fragmentation in a Heap

To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. This redistributes the data while the clustered index is created. This also makes it as optimal as possible,

4. Identifying missing, duplicate or unused indexes

5. monitoring remaining disk space

6. monitoring performance

7. Check database integrity
DBCC CHECKDB

8. Remove older data from msdb
sp_delete_backuphistory
sp_purge_jobhistory
sp_maintplan_delete_log

and much more......

Which index to use,if one covers another?

This is from an interview question I received.

A table has column A with two indexes.

indexA(column A) 

indexB(column A + multiple columns)

I think this will depend on how the query is written and how other indexes are defined on the table. to verify that, I did experiments.

0. Preparation

create table test(id int identity,a int, b int, c int)
go
insert into test(a,b,c)
select ROW_NUMBER() over (order by a.object_id),RANK() over (order by a.object_id),DENSE_RANK() over (order by a.object_id)
from sys.columns a cross join sys.columns b
go
create index ix_a on test(a)

create index ix_a_b on test(a,b)
go
dbcc show_statistics (test,ix_a)
dbcc show_statistics (test,ix_a_b)
go

Then check the execution plan of queries under different situations.

1. select * from test
This ends up as table scan as expected.
With clustered PK: clustered index scan because table is now the index.

2. select * from test where id<100
Since no index is created on id yet, so table scan is expected. and it is
With clustered PK: clustered index seek instead of table scnan.

3.select * from test where a<100
Since a is in both where and select list, index seek on ix_a_b is expected. it chooses wider index because the covering column b is in the select list.
With PK: same ,  but of cause now it has a key lookup instead of rid look up
4. select a from test where a<100
    select a from test where a=100
Here has an interesting observation. In this test, the optimizer chooses ix_a_b over ix_a.
With PK: same

In my another testing, when the table is very wide and no other indexes, it chose ix_a over ix_a_b.
But when a clustered PK was created, the optimizer chose ix_a_b over ix_a.

4.1 Update the statistics and recheck the plan.
update statistics test
it still chooses the ix_a_b

5 Comparison when cost is the same

5.1 with covering index, it chooses wider index.
select a from test where a=100
select a from test with(index(ix_a)) where a=100
it chooses ix_a_b over ix_a

5.2 with uncovering index, in chooses which ever created the first.
select a,c from test where a=100
(select a,c from test with(index(ix_a)) where a=100 to check the cost is the same)
drop index test.ix_a
drop index test.ix_a_b
--change the creation order
create index ix_a_b on test(a,b)
create index ix_a on test(a)
select a,c from test where a=100

6. select a,b from test where a=100
As expected, it chooses ix_a_b since this provides covering on select list.
 With PK: same

Next, Let's experiment how the indexes are used in joining.

7.select a.a from test a join test b on a.a=b.a
The optimizer is smart enough to choose ix_a for both tables

With PK: same

8.select a.a,a.b from test a join test b on a.a=b.a
The optimizer is smart enough to choose ix_a_b for a and ix_a for b.
 With PK: same

9 select a.a,a.b,a.c from test a join test b on a.a=b.a
This time, table scan for a, ix_a for b. As Expected.

With PK:
table scan becomes index scan
Next, let's take a look at how other indexes effect the optimizer's decisions.

10. build PK on id
Alter table test add constraint pk_test_id primary key clustered (id)

The go back to check the execution plans for the situations having been discussed.

Conclusions:



The optimizer choose indexes based on how it caclucate the cost. it calculate the cost
depending on how the query is written, how wide the table is, how wide the index is and
what other indexes/key are defined on the table. Basically, it depends on how the cost is
calculated and a cost is from several aspects such as disk IO and CPU usage.

In a wide table with huge number of records, if these are the only two indexes available, for
the query like “select a from test where a=100”, the optimizer will use ix_a. For a query like
“select a,other columns in ix_a_b from test where a=100”, the optimizer will use ix_a_b since
it provides more cover of the columns in the select list.

In a narrow table, if the cost is similiar, the optimizer chooses wider index over narrower index.
E.G. choose ix_a_b over ix_a on “select a from test where a=100”.

Table Valued Function and Inline Table Valued Function

Inline user defined table valued function is a subset of user defined table valued function. It can be used to achieve the functionality of parameterized views.

Simply, inline table valued function RETURNS TABLE and usually contains only one SELECT statement. Table valued function returns a table data type,   and it can contain additional statements that allow more powerful logic than is possible in views.

Example 1: Inline table valued function

CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT s.Name AS Store, a.City
        FROM Sales.Store AS s
        INNER JOIN Person.BusinessEntityAddress AS bea 
            ON bea.BusinessEntityID = s.BusinessEntityID 
        INNER JOIN Person.Address AS a 
            ON a.AddressID = bea.AddressID
        INNER JOIN Person.StateProvince AS sp 
            ON sp.StateProvinceID = a.StateProvinceID
        WHERE sp.Name = @Region
       );
GO

Example 2: Table valued function

In a table-valued user-defined function:

   1.The RETURNS clause defines a local return variable name for the table returned by the function. The RETURNS clause also defines the format of the table. The scope of the local return variable name is local within the function.

   2. The Transact-SQL statements in the function body build and insert rows into the return variable defined by the RETURNS clause.

    3.When a RETURN statement is executed, the rows inserted into the variable are returned as the tabular output of the function. The RETURN statement cannot have an argument.

    4. No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.