How many plans will SQLServer cache for the same query :)

Hey everyone,

Check out this interesting piece in relevance to Adhoc query caching.

Howmany plans you think will sql server cache for below queries.

SELECT * FROM Production.Product where name ='Adjustable Race';
GO
SELECT * FROM Production.Product where Name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
select * from Production.Product where name ='Adjustable Race';
GO
--here we go
select * from Production.Product where name ='Adjustable Race';
GO

Well, you can verify your answer by asking the DMVs as below.

select usecounts,cacheobjtype,objtype,text from
sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(plan_handle)
where text not like '%dm_exec_cached_plans%'
and cacheobjtype='Compiled Plan' and objtype='adhoc'

What is the value you have for below query? and what is the relationship?


select * from sys.configurations
where name ='optimize for ad hoc workloads'

Try it on your own(ofcourse, in your own instance :) ) and dig-in to understand the adhoc query caching behavior in SQL Server.

EXEC sp_configure 'show advanced option', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

What the hell happened to other plans ??

Cheers!!

Source: Microsoft sql server 2012 internals by kalen delaney

How you fool the sql server plan optimizer for better performance :)

Dear all,

Susantha Bathige who is one of my mentors, shared this link with me. I believe it is very useful.

Query Tuning Mastery: The Art and Science of Manhandling Paralellism
by Adam Machanic

https://www.youtube.com/watch?v=_IRWvlSQxS8&feature=youtu.be

Thanks Adam and Susantha.

Hyperthreading against SQL Server

Well, The correct answer to all interview questions is valid for this topic as well.

IT DEPENDS :)

If we assume a box with 8 Core CPUs with 8MB of L3 Cache (as can be seen in the picture below) each core gets 1MB of cache. When Hyperthreading is enabled you have 16 cores (logical processors) and a core has only 0.5 MB of cache left. 

Well It definately cuts Cache per Core but You have to test it with your workload.
It all depends on how your system behaves with HT enabled or not. :)


I found this article useful, about L3 Cache
http://www.extremetech.com/computing/55662-top-tip-difference-between-l2-and-l3-cache
 and thanks http://www.brentozar.com/ for the original explanation.




Cheers!!

Contention in TempDB

I wanted to give out some thoughts about issues related to SQL server TempDB, but it appeared that most of the useful information are found in the below KB article.

https://support.microsoft.com/en-us/kb/2154845 

However, I have explained some of them which could also have been explained in the above KB article.

• One of the main reasons for contention issues in TempDB is that TempDB is shared across all databases in the instance.

• PageIOLatch (Thread waiting to be read from the disk) and PageLatch (Multiple threads competing access for a in-memory page) wait types pointing to TempDB usually notifies us about this problem.
• PageIOLatch wait types can be avoided by having multiple datafiles as per the recommendations in the KB article, But remember if you see memory spills to tempdb which causes performance issues and if you are having large number of datafiles YOU MIGHT BE HAVING TOO MANY Datafiles!!
• PageLatch waits which occured mostly in SQL Server 2000 are now tweaked from SQL Server 2005 & onwards. However, if you think you are still suffering from them you can go with the trace flag T1118 which is explained in the kb article below. It turns off the use of mixed extents in TEMPDB ONLY.

https://support.microsoft.com/en-us/kb/328551


Cheers!!

SQL Server Database in Suspect Mode!!!!

Hey Everyone,

What if you suddenly get login failiures to a database and when u check it says the database is in SUSPECT mode??

As per msdn, suspect mode is 

At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.

If you have a huge database which demands high up time, DBCC Checkdb to identify the root cause may not be the best solution. DBCC CheckDB can be time consuming.

--to check the status of checkdb

select dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
,percent_complete,blocking_session_id,* from
sys.dm_exec_requests where session_id=sessionID_goes_here
or blocking_session_id=sessionID_goes_here

--to identify any memory dumps created
SELECT top 2 *
FROM sys.dm_server_memory_dumps
ORDER BY creation_time DESC

If you have a smaller database you could follow below steps to recovery your database.

1. EXEC sp_resetstatus 'DBName'
2. ALTER DATABASE DBName SET EMERGENCY
3. DBCC checkdb('DBName')
4. If you get errors from checkdb=> ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5. DBCC CheckDB ('DBName ', REPAIR_ALLOW_DATA_LOSS)
6. ALTER DATABASE [DBName]  SET MULTI_USER

However, If your database is terabytes in size and unable to run dbcc checkdb, you could use dbcc checktable option on the suspicious tables to isolate any corruption.But the drawback is in EMERGANCY mode you are unable to execute DBCC Checktable ('tableName', REPAIR_ALLOW_DATA_LOSS).

We came across a simmilar situation recently.


Point in time recovery was useless since the corruption comes with the log backups, you need to identify the point before the corruption and restore until then to solve the issue which is not practical and can cause huge data loss.


Our approach which was suggested by microsoft was to rebuild the log file at the cost of losing data in the transaction log.

Simply you have to detach the database and attach it without the logfile. A new log file will be created and mapped to the database.

This way you can get the database online.

Remember to run a DBCC CHECKDB followed by the log rebuild and repair any inconsistancies.

Cheers

Dropping a user from All SQL server databases

I Know its a simple thing, It is None other than sp_MSforeachdb. That is all you need to execute.

Below is a sample code:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC  sp_dropuser ''UserNameGoesHere'''

This will throw error messages for databases where user does not exist.

I was just too lazy to write the code to avoid the error since, it was doing the task.

Cheers.

Troubleshooting schedules after SSRS migration

You might come across situations where you can't create new schedules from your report manager.

Common symptoms are when you create new schedules it goes to an error page.

In order to trouble shoot, launch the report manager web page via your SSRS box. It throws the acutal error message.

One of the Common messages is listed below.

Reporting Services error: “RSInternalError: Invalid object name 'ReportServerTempDB.dbo.PersistedStream'."

Check the references to specified database in the error messages. If the report server does not have the database mentioned in the error message it is still refering to databases prior to migration.

In other words check if any of the stored procedures or triggers are reffering to a non-existant database. 

Alter the triggers and storedprocedures to match with the new report server temp database.

Your should be able to sucessfully create schedules then.

Cheers