Well, I am not as qualified in SQL Server as the authors of this book (Pinal Dave & Vinod Kumar) though I dare to write my review for his book as both of the authors are in my favourite list and I don’t even miss any article written by them then how could I miss a book written by them? After reading the book, I really tempted to share my personal opinion with my blog reader.

“SQL Server Interview Questions and Answer” makes first impression that this book is written to prepare you for the interview of DBA or SQL Developer or BI professional but NO, this book is not ONLY for those who are preparing for an interview, even seasoned developer or DBA could refer this book to master the basics which we may avoid or forget over the time but knowing those may create a firm ground for the project we all are working on.

Both of these authors are well known to present hard & difficult concept in very simple yet powerful manner which directly execute INSERT command in your memory without any trigger or exception, just like strait thrown dart pinch in board, this really show very hard work of both authors.

I really impressed with the book for following points.

• “Points to Ponder” section at the end of each chapter as a Quick references to Joes 2 Pros books (I had privilege to read few of them)
• Very inspiring quote at the begging of chapters (I enjoy it in Vinod Kumar’s twits too, in twitter)
• Links to SQLAuthorty’s articles

When I first heard about this book, I though how much more content could be there in this book? As Pinal has already written the series articles on this topic but with my surprise, there are LOT MORE to learn in book so even if you have read all articles on this series in Pinal’s blog, don’t hesitate to have this book, you will have so many (80%) new stuffs to look at.

This book scores 10/10 and I personally highly recommend this book as a good & quick reference to any professional who are dealing with SQL Server at any level.

To order the copy of the book for your own, visit SQLAuthority.com

Happy Reading!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Msg 605, Level 21, State 3, Line 1

Attempt to fetch logical page (3:1307866) in database 6 failed. It belongs to allocation unit 72057594113359872 not to 72057594113490944.

Or

Msg 21, Level 21, State 1, Line 1

Warning: Fatal error 605 occurred at OCT  31 2011  6:08AM. Note the error and time, and contact your system administrator.

This error comes in red color is disguise, if you login with any user which is not sysadmin type user, you will see this kind of error in corruption of page(s), if you login with SA, Administrator or and other SysAdmin login, you will not see error comes in red color but the descriptive error given in the title

There was a festival holidays in our Indian office from 26^th to 28^th OCT 2011 (Wed/Friday) so obviously I was out of town and back after 5 days on 31^st OCT 2011. As soon as I come to the office and checked few of my email, I suddenly get complain that few pages in our software are throwing an error so I picked up the query which was there in those pages along with the same parameter they were providing and found the error given above.

 

As soon as I read this error, I came to know that there is a page corruption in database.  It may be due to heavy snow fall in NJ in October, they have got power failure there from few days and tried to keep the server up and running via power generator. As soon as Power Generator’s fuel get finished, everything gets down and after refuel, they starts everything again. I came to know this as soon as I come back from holiday. I think this is the Indian New Year gift to me.

I tried to gather some more information about the data file and page number given in error message with DBCC PAGE command.

dbcc traceon(3604)

dbcc page('MyDatabaseName',3,1307866,3)

dbcc traceoff(3604)

You can use option 0,1,2,3 as the last parameter (bold 3) of DBCC PAGE command.

So now I have two options.

1.)    Restore that page from full Database backup

2.)    Try DBCC commands and if needed, allow data loss

I HIGHLY RECOMMEND to go for 1^st option but if you are out of luck and don’t have last full backup, you must have to go for second way.

I have restored page from my last full database backup with below given TSQL.

RESTORE DATABASE MyDatabaseName
PAGE = '3:1307866'
FROM DISK = 'D:\MyDatabaseName.bak'
WITH NORECOVERY

Now, let us talk about second option if you don’t have full backup of your database. Use DBCC CHECKDB command.

--checking database's integrity and won't show so many informational message,

--it will only shows error messages and warnings.

DBCC CHECKDB ('MyDatabaseName') WITH NO_INFOMSGS

Once you execute above command, it will recommend you repair level. it may recommend REPAIR_REBUILD if you really lucky but if you will see repair level REPAIR_ALLOW_DATA_LOSS, you have to be ready to lose some of your data.

You may use either of the below given command based on the Repair Level you have been suggested.

1.)

ALTER DATABASE MyDatabaseName SET SINGLE_USER

GO

DBCC CHECKDB('MyDatabaseName', REPAIR_REBUILD)

GO

ALTER database MyDatabaseName SET MULTI_USER

GO

2.)

ALTER DATABASE MyDatabaseName SET SINGLE_USER

GO

DBCC CHECKDB('MyDatabaseName', REPAIR_ALLOW_DATA_LOSS)

GO

ALTER database MyDatabaseName SET MULTI_USER

GO

Moral of the story is, always have FULL recovery model for your database and schedule full/ transaction/ differential backup policy. This is a MUST DO for any DBAs. You never know, when will you need it!!!!

Reference: Ritesh Shah
 

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

My previous article was on Compilation & Recompilation of Stored Procedure. After reading that article, two of my reader sent me an email about more clarification on some terminology and that is the base of writing this article.

Actually SQL Server Query Optimizer is really one of the smart tools which used to find or generate best execution plan for query or stored procedure and make the task easy for DBA. Basically it uses Cost Based Optimizer (CBO) to do this task, even sometime feels to have specific Execution Plan for query so Microsoft have provided many different hints at different level.

Query Optimizer (QO) used to save execution plan when it compiles Stored Procedure first time and use the same execution plan when the same stored procedure is being used again so that you can get rid of overhead of generating execution plan again and again. 

When index statistics changes heavily, QO used to recompile the statement to get best plan for your stored procedure but if you are sure that the existing Plan would works fine than one should go for OPTION KEEPFIXED PLAN for that statement so that you can save your resources and boost up performance.

I will demonstrate this with few queries run in SQL Server Management Studio (SSMS) and capture the results in Profiler. 

I will select following Events in Profiler while creating new Trace for my server.

Stored Procedure

• SP:Completed
• SP:Recompile
• SP:smtpCompleted
• SP:smtpStarting

 

Now execute following TSQL in your SSMS.

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--creating nonclustered index

CREATE NONCLUSTERED INDEX IDX_ORD ON Orders(amount)

GO

--inserting only 1 record in our table

INSERT INTO orders VALUES(GETDATE(),1001,111)

GO

--creating SP which will SELECT all records from Orders table where Amount is 1001

CREATE PROC spGet_ord

AS

SELECT * FROM orders WHERE Amount=1001

GO

--execute SP

exec spGet_ord

GO

If you will see in profiler than you would get following event captured

Now, make change in Index statistics by inserting so many records in table so that we can test the effect in profiler after executing SP again.

--inserting 50000 fack rows into table

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--execute SP again

exec spGet_ord

GO

Now, if you see the profiler, you will see “SP:Recompile” event as your statistics are updated so it need recompilation of statement. If you again few more thousands record in same table and execute the same SP again, you will see recompilation again.

Now, after droping table and SP, we will make one small change in Stored Procedure and will use “KEEPFIXED PLAN” hint so that we can avoid recompilation.

drop proc spGet_ord

GO

drop table orders

go

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

CREATE NONCLUSTERED INDEX IDX_ORD ON Orders(amount)

GO

INSERT INTO orders VALUES(GETDATE(),1001,111)

GO

CREATE PROC spGet_ord

AS

SELECT * FROM orders WHERE Amount=1001

--adding below statement other than that, same code as previous SP

OPTION (KEEPFIXED PLAN);

GO

exec spGet_ord

GO

After recreating table along with fresh records, we are going to see what happened while executing SP in above statement.

Since our table now has only 1 record so it is time to change statistics with bulk insert as follows again.

--inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 50000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

exec spGet_ord

GO

--if you wish, you can uncomment below code and delete SQLHub database

--use master

--go

--drop database sqlhub

Here is the screen capture of Profiler which doesn’t show Recompilation.

 

You can even add few more thousands of rows and execute SP again, you won’t get recompilation. 

Reference: Ritesh Shah
 

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

I generally ask one question in interview “If we have one complex query which JOIN five table, what would work best from .NET application, Call of Query or Stored Procedure?” 80% of candidate used to say “SP works fast”. My next question always would be “WHY?” than out of those 80%, 60% would say “Because SP is a compiled code”. After that I fire my follow up question, I can see option of “Compile page or project in .NET but I never see that kind of option in SSMS, how do you compile your SP?”, once I EXECUTE this question, 90% candidate of those 60% candidate would like to keep silence or divert the talk.

Anyway, intention of this article is to let you know some facts about SP compilation & Recompilation. Since it is big topic and wouldn’t cover under on article, I may come up with some more articles on this topic but right now, let me explain some basic facts only.

First of all, let me tell you that you don’t need to compile Stored Procedure manually, when you execute it, SQL Server will compile your Stored Procedure for you and save the execution plan for future use so that it doesn’t need to compile again and again, this is generic understanding, it doesn’t work all time as few facts are there which cause recompilation many time or every time. If you want to recompile your Stored Procedure manually, you should use “SP_Recompile” Stored Procedure given by SQL Server.

Now, you think if recompilation is that bad than why Microsoft has given facility to recompile? Well, let me have an opportunity to break the ice, recompilation of stored procedure is not always bad. It may be beneficial or may be harmful, it is totally depends on the situation.

Actually compilation of Stored Procedure stores the execution plan first time you execute your Stored Procedure and every follow up call would use the same execution plan but recompilation of SP would be helpful if you have new statistics or new index on the table. BTW, in SQL Server 2008+ there is in-built functionality to recompile at statement level rather than recompiling whole stored procedure which is less resource centric. 

Following is the list of basic cause which forces Stored Procedure to recompile.

·          

• Change in SET option within Stored Procedure
• Execution plan is very old
• Schema change in table, index, view or temp tables which are used in Stored Procedure
•  “Deferred object resolution”, means object was not available while compiling Stored Procedure  but you have created later on, may be some temp table you have created in Stored Procedure.
•  Call of “SP_Recompile” Stored Procedure.
• Call of RECOMPILE clause in Stored Procedure.
• Statistics are old

How to avoid Stored Procedure recompilations?

• Avoid using temp table or other DDL statements as long as possible.
• Use table variable in Stored Procedure if needed
• Avoid changing SET option in Stored Procedure like ARITHABORT, Quoted_Identifier, ANSI_NULLS, ANSI_WARNINGS etc.
• Avoiding recompilation by statistics change by using “KEEPFIXEDPLAN” hint.
• Disable Auto Update statistics for your database.

Well, these are very basic understanding and each point of this article may consume separate dedicated article and I may come up with series on this very soon.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

SQL Server MVP/Auther/Trainer Jacob Sebastian has launched another quiz “SQL Server DBA Quiz 2011” which is the last quiz of this year. So don’t miss this chance and answer the quiz question asked by Quiz master.

My question is already published regarding Dead Lock, Live Lock and Blocking. If you have not participated yet, still, you have chance to win as all questions are still open. 

If you are not seasoned DBA, still you can refer all questions and answer given by expert to enhance your knowledge.

If you are experienced DBA and you know the answer, you can answer the question, share you knowledge with community and have chance to win iPad. So what are you waiting for? Try to answer as more question as possible.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”