Sql Server: Use FREEPROCCACHE & DROPCLEANBUFFERS to free up plan cache and buffer pool

Posted by Sqltimes
On September 28, 2013
0 Comments

A quick one today; During development, I often find myself having to clear up SQL servers procedure cache before re-running a query. Sometimes I have a hard time recollecting the exact order of words. Is it ‘FREEPROCCACHE’ or ‘PROCCACHEFREE’. So this post is for me to refer to, in future. 1 2 3 4 5 […]

Posted by Sqltimes
On September 21, 2013
0 Comments

Just learnt an awesome feature with in SQLCMD. String Replacement using SQLCMD !! Using -v we can replace strings in the sql file that is about to be executed. This may not sound great, when you are in a unique situation where you need to replace a particular section of your SQL code with a string literal on […]

Posted by Sqltimes
On September 19, 2013
0 Comments

Update : Added one more way; Hat tip to Glenn Berry Running COUNT(*) on a table results in Table scan or Index scan; Both are not preferred. Meta-data views could help avoid that. Option 1: Query ‘dm_db_partition_stats’ view [sourcecode language=”sql”] SELECT T.name AS [Table_Name] , I.name AS [Index_Name] , SPS.row_count AS [Record Count] FROM sys.tables […]

Posted by Sqltimes
On September 7, 2013
0 Comments

Starting Sql Server 2005, tempdb’s role has significantly increased. It is heavily used for data processing i.e MARS, sort, reindexing, temp tables, table variables, etc. Performance Tuning TempDB is not just an important task its a non-ignorable task for any large scale system (VLDB or evolving VLDBs). There are several best practicies to make overall […]

Posted by Sqltimes
On September 3, 2013
0 Comments

Recently, I made a post on why it takes a lot of time when you run CREATE DATABASE (or other data file extension tasks). The reason is database file initialization. This initialization happens for both LOG and DATA files, but you can skip this step for DATA files (not LOG files). Recently, I learnt from […]

Sql Server: Use FREEPROCCACHE & DROPCLEANBUFFERS to free up plan cache and buffer pool

SQLCMD perform string replacement with parameters. Awesome Feature !!

Sql Server: Get count(*) on all tables with very little overhead (without table scan), by querying metadata

Sql Server: How many data files do I need for TempDB? For Heavy traffic environments

Instant Database File Initialization: Make it faster (or skip it altogether)