Shrink TempDB Data File Without SQL Restart

We can shrink the tempdb data file without restarting the SQL Server using the following steps.

First verify the space usage information from MDF file, If you find there is enough space to shrink then ensure there wont be any open transactions running on the tempdb and execute the below steps to shrink the TempDB Data File.


DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (TEMPDEV,1024)
GO

Which query was slower?

Some time when you try to win some arguments with your seniors you need some valid points and numbers to prove that you know exactly what you say.

The same happened with me several time, that what I was saying was correct but it was flying over head of my manager, so I thought when I will come next time to discussion with him, I will bring some proofs to shut his mouth. 

So one day when I was fighting for to push some optimized work to production, he asked me do you have a list of the queries that is performing slow on the production DB?

I thought for a second "NO", but I say yes I have, although it was not difficult to cheat on, I can again take a snapshot of the activity monitor on production server and show this is the list, but I start looking to find it from with in SQL Server (I was more then 100% sure that SQL server must remember the slow performance queries). 

So here is the final query I came with to find the slow queries....


SELECT TOP 20 
SUBSTRING(EST.text, (EQS.statement_start_offset/2)+1, 
        ((CASE EQS.statement_end_offset
          WHEN -1 THEN DATALENGTH(EST.text)
         ELSE EQS.statement_end_offset
         END - EQS.statement_start_offset)/2)+1) As Query, 
EQS.execution_count, 
EQS.total_logical_reads, 
EQS.last_logical_reads,
EQS.min_logical_reads, 
EQS.max_logical_reads,
EQS.total_elapsed_time, 
EQS.last_elapsed_time,
EQS.min_elapsed_time, 
EQS.max_elapsed_time,
EQS.last_execution_time,
EQP.query_plan,
DBS.name
FROM sys.dm_exec_query_stats EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) EST
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) EQP
Left Outer Join sys.databases DBS ON EST.dbid = DBS.database_id
WHERE EST.encrypted=0 And DBS.name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY EQS.total_logical_reads DESC


Nothing is new !!!
CROSS APPLY => is used as Inner Join with Tabled value functions 

Half cooked concepts are dangerous

Well I am not sure, what it called in English or any other language but in Pashto we say, A half scholar is deadly danger for your knowledge, and a half doctor is deadly danger for your health.

The same principle apply to every field including database designing, I came to this conclusion when I encounter such practice I am currently analyzing for optimization.

Some people use a technique for publishing data, the data that is formatted for some particular purpose to be access with optimum performance, for this purpose the removed some checking constraints, and de-normalize it. 

De-normalize means you will have no need to put join in your query and all your desired data will be in a single table, and that table might be used for a single purpose. The constraints are removed for faster writing, because you might be updating it too quickly in a background/a-sync process, as well the query used for extracting information might be expensive.

But I have seen example of joining tables that were de-normalized and some constraints were removed, the performance of was too bad, there was a lot of I/O read, CPU utilization and more then 20K Operator  cast. 

I was technically not allowed to make some physical design changes in the current system, so I had to stay with the current design and optimize it to some acceptable level. 

I put some clustered and non clustered indices on main table and on associated tables. I get some 40% performance, and when I put some statistics on few columns I was amazed by the performance I got 85 to 90% performance gain.

The conclusion is never max premature concepts and avoid using publish table for different purposes, what I suggest always use a single publishing table for a single purpose.    

Publishing Tables Issue

Today I encountered a very straight forward SQL Query  a simple Select with columns list, and order by an integer column. I wondered because there was no such big columns in the selection list, and even there were only a few thousands of records.

 When I analyzed the underlying table I cam to know that it was a publishing table (as it belonged to pub schema), which does not has any separate primary key nor it inherit it from some other tables, so in short there were no Clustered Index neither Non Clustered Index. Here is the execution plan details.

I experiment on that same table I created another table with an Auto ID columns to be used as primary key. So I just got approximately 4 to 7 percent performance as follow.  

So...I was not happy with this performance, I dig a bit on the other side, instead of creating another table, move data in it, was too much for that little performance boost. So I tried another thing why not I should create a clustered index on the table for the column it is sorting the data, and I did it and I got only 2 % percent performance gain.

And finally I got some thing that why I should not try Non clustered index on the same column, so I created a Non Clustered index as follow.

CREATE NONCLUSTERED INDEX [NCI_SequencNo_MyTable] ON [pub].[MyTable] 

(

[SequenceNo] ASC

)

INCLUDE ( [ID],

[Name],

[IsInActive],

[IsSuspended]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

And I got a lot of performance....look at yourself...

I tried another thing, why not to remove the clustered index I created before which did give me only 2 % performance, and see if I can get more. Here is the final execution plan details I got.

And the most beutiful thing was in the execution plan I was not seeing the sorting details, seems it was removeing the Order By clause from the query, as it understands the result will be sorted..

Number of rows per table

Well first thing first, when you have to analyze a database, you might want to see how big it is, and how much records are there in each table.

One way is to get count for each table like

Select Count(0) From Table1;

But if you have more then 100 tables in the system it will be very difficult to do so, so you might need a query which can list all the tables in the database with its row count, thanks to the master database which store all these info for you. Here is the query which will bring the list for you.


Select 
sob.name,
dbps.row_count
From sys.dm_db_partition_stats dbps
Inner join sys.sysobjects sob on sob.id = dbps.object_id
Where sob.type = 'U' And dbps.index_id = 1

About this Blog

My Manager just assigned me a task to optimize our databases, so I decided that while I am doing any optimization to the database I will write a blog about each step, so it will be helpful to general public and our team if we were able to make any breakthrough.

 Keep fruit out where you can see it. That way you’ll be more likely to eat it. Keep it out on the counter or in the front of the fridge.