Stored Procedure SP_INDEXOPTION In SQL Server 2012

Today I find out the next sentece in SQL 2012

EXEC sp_indexoption ‘XXX.YYY’, ‘disallowpagelocks’, TRUE

This database is has been migrated recently, was previously in version 2005, but we have done an upgrade to 2012. The issue is that looking on Technet where recommended not to use or avoid, where possible using this function, since it could disappear in future releases. This is one of the problems customize performance optimization. The recommended alternative is to use ALTER INDEX.

Please refer at Technet Microsoft SP_IndexOption

HTH – Antonio NAVARRO

 

 

How To Flush An Execution Plan For A Statement On SQL Server

Many times, need to probe differents executions plan for the same statement where perform tuning. It is a good practise flush the previous plan before. You can flush the plan from the cache with DBCC command. This testcase is on SQL Server 2012, it works on 2014 too.

First, locate the sentence what you want flush and its plan_handle, you can do it with the following query;

SELECT 
   PLAN_HANDLE, 
   B.TEXT
FROM 
   SYS.DM_EXEC_CACHED_PLANS 
   CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) B
WHERE 
   TEXT LIKE N'%SELECT COUNT (*) FROM MY_TABLE%';   -- HERE YOU MUST SPECIFY YOUR QUERY OR SUBSTRING

Second, with the plan_handle got, execute the DBCC command

DBCC FREEPROCCACHE (0x060005001D724F342057CE2B0200000001000000000000000000000000000000000000000000000000000000);

If you execute the query from first step, it returned nothing.

HTH – Antonio NAVARRO

How To Analyze Stats Without Histograms

Today, a coworker ask me about how take stats on tables without histogram. The next sentence get analyze the table without statistics;

exec dbms_stats.gather_table_stats(user,tabname=>’MYTABLE’,method_opt=>’FOR ALL COLUMNS SIZE 1′,estimate_percent=>100,cascade=>TRUE);

You can take not histograms by columns, for this task please modify the clause method_opt, like the following example;

method_opt => ‘for columns <your_column> size 1’

In my opinion histograms are good in columns, specially where it is used for join. I think you have a good reason for not use them.

HTH – Antonio NAVARRO

 

 

SQL Server Database In Suspect Mode

Today a coworker has problems with SQLServer 2005, it seems a node had a crash (two-node cluster) and after a several minutes the other node get crash too. When start the instance one of the databases stay in “suspect” mode. Applying the logic as Microsoft documentation indicates we should go into “emergency” mode and launch a dbcc checkdb. The problem is that this database It is of a size of several Teras, so an estimate that checkdb may take more than ten hours, too long for a production database operating in 24×7.

There is a tape backup and also several snapshots cabin is made, this cluster using NetApp, so restore the last snapshot is question a few minutes and applied recovery from there. Finally this is the chosen option because as I say is a matter of minutes. If you haven’t this technology, provided by the cabin would have to try a DBCC with REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD options (you need set emergency mode), but these options are not recommended since it may lose data.

As a final option would have to go to the last backup, remember that databases should always be in full recovery mode, and apply corresponding logs.
Possible causes that produce a database get suspect

The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.
Please check all these items befores start a restore/recover database.

HTH – Antonio NAVARRO