Data Compression in SQL

Over a time, I have learnt that IO is critical resource in all the cases. I have seen it being bottle neck in lots of scenarios where we have plenty of CPU and Memory.

May be thats the reason I see lots of enhancements in terms to use IO resources at their best... database compression in SQL Server is one of its best feature. Following link is from my past learning, where I learnt how database compression feature in SQL Server benefits all of us. This will surely help all of us to understand how compression helps.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/do-you-get-a-benefit-from-compressing-ba

One thing that everyone should not forget is, each feature comes with its own advantages and disadvantage too. While going with advantages, one shouldnt miss to evaluate its disadvantages.

I appreciate the efforts done by SQLDenis.

Regards,
Sajid

SQL Server 2012 (Denali) : AlwaysOn vs Mirroring

I was trying to understand AlwaysOn feature in SQL Server 2012, releasing very soon. During my learning I found below article where author Michael Otey compared the AlwaysOn feature with Mirroring.

http://www.sqlmag.com/article/sqlserverdenali/sql-server-denali-alwayson-140199

It's worthy to read.

Regards,

SP:Recompile Event - finding out the reason for Stored Procedure Recompilation

When tracing SQL Stored procedure recompile event (SP:Recompile), it may find helpful to know why procedure recompiled. This can be determined through the value in column EventSubClass for SP:Recompile Event. (see capture)

Following sub classes can be found for SP:Recompile event.

statistics changed

SubClass Name	SubClass	Detailed Reason for recompilation
Schema changed	1	Schema, bindings, or permissions changed between compile and execute
Statistics Changed	2	statistics changed
Deferred compile	3	Recompile because of DNR(Deferred Name Resolution). Object not found at compile time, deferred check to run time.
Set option change	4	Set Option changed in batch
Temp table changed	5	Temp table schema, binding, or permission changed
remove rowset changed	6	Remote rowset shcema, binding, or permission changed
query notification environment changed	8	NEW in SQL Server 2005
partition view changed	9	SQL Server 2005 sometimes adds data-dependent implied predicates to WHERE clauses of queries in some indexed views. If the underlying data changes, such implied predicates become invalid, and the associated cached query plan needs recompilation. (New in SQl Server 2005)

SP:Recompile Event - finding out the reason for Stored Procedure Recompilation

When tracing SQL Stored procedure recompile event (SP:Recompile), it may find helpful to know why procedure recompiled. This can be determined through the value in column EventSubClass for SP:Recompile Event. (see capture)

Following sub classes can be found for SP:Recompile event.

SubClass Name	SubClass	Detailed Reason for recompilation
Schema changed	1	Schema, bindings, or permissions changed between compile and execute
Statistics Changed	2	statistics changed
Deferred compile	3	Recompile because of DNR(Deferred Name Resolution). Object not found at compile time, deferred check to run time.
Set option change	4	Set Option changed in batch
Temp table changed	5	Temp table schema, binding, or permission changed
remove rowset changed	6	Remote rowset shcema, binding, or permission changed
query notification environment changed	8	NEW in SQL Server 2005
partition view changed	9	SQL Server 2005 sometimes adds data-dependent implied predicates to WHERE clauses of queries in some indexed views. If the underlying data changes, such implied predicates become invalid, and the associated cached query plan needs recompilation. (New in SQl Server 2005)

How SQL Server is better RDBMS than other?

Recently Pinal Dave – SQL Server MVP, conducted a survey on ‘How SQL Server is better RDBMS?’. He offered his MSDN Subscription (worth $11,899 (USD) ) for best comments. To join him Madhivanan – SQL Server MVP also offered his MSDN Subscription to the best comments as well. If you are interested the Pro’s of SQL Server over other RDBMS applications, I would suggest to visit Pinal’s blog linked above.

Since this was a chance to express my thoughts on SQL Server, I provided my thoughts on SQL Server and my comments got selected as best comments…. You can see my comments on Pinal’s Blog here

Pinal, Madhivanan and Jacob Sebastian together have made my day today. Thank you Pinal!!!

Regards,

Sajid

Resetting SA password- DAC

I have been asked a question by couple of friends and colleagues to reset or obtain the password of SA login in SQL Server.

It happens with lack of proper documentation or standards, that password of SA is forgotten. This is very big hectic if no windows user assigned to admin privileges, through which you can reset the sa password. If it’s not then earlier there were no much options and one had to move the database files to other disk and reinstall the SQL server and hell lot of stuff.. that required your time and patience.

Thanks to DAC, Dedicated Admin Connection, feature introduced in SQL Server 2008. It allows “Windows Admin User” of hosted server to start the SQL Server with single user mode (switch -m), which inherits the admin privileges to that user, through which one can reset the sa password and can grant access the administrative rights to other users.

NOTE: One should have EXTRA precautions while granting LOCAL Admin rights to any Windows login.

How to retrieve parameter value from Saved Execution Plan

When you save the query execution plan to analyze later. You can get the parameter values passed for that execution either from XML plan or Query Plan properties.

To get the parameter values through xml plan …

Right click on LEFTMOST operator of the query plan (in my case it is SELECT)

Select “Shoe Execution Plan XML…” option

Find “<ParameterList>”

There you will find the parameter with their run time value. (as shown below)

To get the parameter values through property window…

Right click on LEFTMOST  operator of the query plan (in my case it is SELECT)

Select ‘Properties’

Find ‘Parameter List’ attribute

Expand the attribute to get the parameter along with its runtime value. (as shown below)

Also notice that you will find the Set Options in Properties window and StatementSetOptions element in XML query plan, that will describe the environmental variable state in which plan was generated.

-Sajid