Monthly Archives: September 2013

Strategizing your Index Maintenance in SQL Server

Index maintenance is part of the DBA’s life. Surely you have a maintenance task for this somewhere running daily or weekly on your server. For the junior DBA, the rebuilding of indexes task will include a scheduled job that will reindex all indexes in the database. Imagine this task running on all your indexes and if your tables have a clustered index in them, which is most likely, this job is actually rewriting your entire database. This is a waste of precious resources on your server, unnecessary read/write activity and never mind the transaction log blowing out of proportions.

There is a more effective strategy for creating a maintenance plan for rebuilding fragmented indexes. The more effective approach would be to reindex only where needed and just leave the rest untouched. If it doesn’t need fixing, then don’t touch it at all.

First step would be to list all indexes in your database, like having an inventory of all indexes. You can query the DMV sys.dm_db_index_physical_stats for this. You may run a similar query below.

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
iss.index_type_descASIndexType,
iss.avg_fragmentation_in_percent AS FragmentationPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) iss
INNER JOIN sys.indexes i ON i.object_id = iss.object_id AND i.index_id = iss.index_id
ORDER BY iss.avg_fragmentation_in_percent DESC

Now that you have a gauge of how fragmented your indexes are, basing from the results of the query above, you can use your better judgement on which indexes to REBUILD or REORGANIZE and rewrite your maintenance task where applicable. To better understand the query results, if the avg_fragmentation_in_percent is less than 5%, then don’t touch it all. If the value is between 5% and 30% then run reorganize with the option ALTER INDEX REORGANIZE. If the avg_fragmentation_in_percent is above 30% then by all means run rebuild with the optionALTER INDEX REBUILD WITH (ONLINE=ON).

The (ONLINE=ON) option allows users to still use the index while the REBUILD task is ongoing. This feature is limited to enterprise edition of SQL Server 2005 and later only.

As a general rule, check your index fragmentation percentage daily or weekly then run your REBUILD or REORGANIZE task as needed only. This way you do not trigger unnecessary activity in your server and waste resources.

For more information on the guidelines, visit link http://support.microsoft.com/kb/2755960


Poor Microsoft Doesn’t Understand What Tablets Are For

That’s the title of an article i came across this morning in Slate.com. I am not a big fan of Surface. I’m totally IOS fanatic from iPhone to iPadMini to using Mac at home.

IMHO, Microsoft has been committing a big mistake with Surface 2 and Surface Pro 2. Of course it comes with performance booster, super long battery life and more cores on the GPU and what have you that’s bigger and better. It comes with the Microsoft Office Suite with Outlook. Now imagine this, maybe in Surface you can open all the office apps on one side and play xbox game on the other side of the screen!

But who does that? Most people I know, if not all, have tablets for entertainment and portability and another PC or laptop for their work stuff. I use my iPadMini mainly for entertainment and Twitter. I watch Breaking Bad series on it while i take my morning commute to work. I use my Mac for blogging and work related emails and docs at home or sometimes in Starbucks. Everywhere I look, I see people on MRT and buses watching their favorite soaps or movies or playing Plants vs Zombies on their tablets.

People who are working continously and so hard at improving Surface should do an hour or 2 of people watching so that they can see how coffee lovers and workaholics use their laptops and tablets in MRT and coffee shops. I believe that this is the real market.

If Microsoft thinks it can change people how they use their tablet for play and work, I don’t see that coming at all. Because come to think of it, the reason why I use my morning commute time to catch up on Breaking Bad series is that I rarely have time to watch it on my work PC because at home I still bring some of my work stuff. Surely this is the case for most of the people out there. Working late at nigth at home or staying late at the office.

I don’t need super powerful hardware on my tablet so I can open all Office apps all at the same time. I need it to be light and portable. I need it to have a long battery life. I just need to catch up on Breaking Bad and Plants vs Zombies and my emails.


SQL SERVER 2012 – Fix – Error : 41202- The source table ‘%.*ls’ specified in the SEMANTICSIMILARITYTABLE, SEMANTICKEYPHRASETABLE or SEMANTICSIMILARITYDETAILSTABLE function doesn’t have a full-text index

SQL Server Portal

I came across this error message while I was working on one of the file tables (A new table concept came in SQL Server 2012) and tried to create a full text index. Most likely this error is related to semantic search (A new type of search concept came in SQL Server 2012).
Let me explain this error in detail :

Message Number: 41202

Severity : 16
Error Message: The source table ‘%.*ls’ specified in the SEMANTICSIMILARITYTABLE, SEMANTICKEYPHRASETABLE or SEMANTICSIMILARITYDETAILSTABLE function doesn’t have a full-text index that uses the STATISTICAL_SEMANTICS option. A full-text index using the STATISTICAL_SEMANTICS option is required to use this function.

Error Generation:
I tried to create full text index on file table and received given below error message.

errormessage41202.1.1

Lets resolve this issue step by step :

Step 1 :
First of all you need to browse the SQL Server installation media and select the given below…

View original post 145 more words


Cumulative update package 6 for SQL Server 2012 SP1

Microsoft has release the CU update package 6 for SQL Server 2012 SP1. To apply this package in your environment, you must be running SQL Server 2012 SP1.

This cumulative update includes about 20 fixes dealing mostly with MDX queries and SSAS cubes. For more information on the updates included in this package, please visit http://support.microsoft.com/kb/2874879.

Because the builds are cumulative, each new update release contains all the hotfixes and all the security updates that were included with the previous SQL Server 2012 update release. The build number for this update is 11.0.3381.0

As a best practice, test this cummulative update in a staging server before proceeding to deploy in your production environment.


Brad Shultz’s blog on reviewing your database

this is a cool article. joining fun and reality

SQL Studies

This is possibly the best blog I have every read on the subject of reviewing a database. It is witty, insightful, and mentions a number of very common problems. The descriptions of each problem are well written and easy to understand. There are multiple scripts and links throughout the post to help with both database maintenance and general review/troubleshooting.

Best of all it’s a quick read (if you don’t spend time carefully reading each script) and keeps your interest from start to finish. In short I HIGHLY recommend taking a few minutes and reading Brad Shultz’s “Shrink Your Databases Regularly”.

View original post


My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with thee Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment