SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Archives: June 2012

Rebuild indexes based on fragmentation – sp_RebuildIndex

In my previous post here, I’ve discussed how we can detect fragmentation in SQL Server databases indexes using dynamic management view function sys.dm_db_index_physical_stats. In this post, I’m sharing my stored procedure which I’ve created a while ago to rebuild fragmented indexes based on the fragmentation level. This stored procedure… Read more

7 comments, 5,316 reads

Posted in Basit's SQL Server Tips on 30 June 2012

Using SQL Server 2012 FileTables

SQL Server 2012 allows you to store file/directories in a special table called FileTable that builds on top of SQL Server FILESTREAM technology. As per Microsoft BOL, “FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services – including full-text search and semantic… Read more

4 comments, 13,132 reads

Posted in Basit's SQL Server Tips on 24 June 2012

SQL Server 2012 Installation Guide

This article walks the user through installation of SQL Server 2012 on a Windows Server 2008 system using the SQL Server setup installation wizard. The installation process is simple, straight forward and is very similar to SQL Server 2008 / 2008 R2 setup. The procedure described is for a non-clustered… Read more

10 comments, 207,679 reads

Posted in Basit's SQL Server Tips on 23 June 2012

Determine Index Fragmentation in a SQL Server Database

Index fragmentation can adversely affect query response time. When accessing data through an index, SQL Server must read each page in the specified range to retrieve the indexed values. If the index is highly fragmented, SQL Server may have to search many more pages, and possibly levels, to get this… Read more

4 comments, 9,068 reads

Posted in Basit's SQL Server Tips on 18 June 2012

Storage options available in Windows for SQL Server

There are several options available for configuring your storage media in Windows Server 2003 and Windows Server 2008. By understanding your storage options will help you to make informed decisions about SQL Server system configuration and data storage. In this blog post we will first take look at what Software… Read more

0 comments, 2,432 reads

Posted in Basit's SQL Server Tips on 16 June 2012

Find unused indexes using sys.dm_db_index_usage_stats

The SQL Server maintains all indexes defined against a table regardless of their usage. Index maintenance can cause significant amounts of CPU and I/O usage, which can be detrimental to performance in a write-intensive system. With this in mind, it makes sense to identify and remove any indexes that are… Read more

2 comments, 9,753 reads

Posted in Basit's SQL Server Tips on 15 June 2012

SQL Server Configuration Manager : Cannot connect to WMI provider – Invalid class [0x80041010]

Following error dialog pops up today, when I tried connecting to SQL Server Configuration Manager on my SQL Server 2008 production server:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with… Read more

10 comments, 24,923 reads

Posted in Basit's SQL Server Tips on 6 June 2012

Unable to recycle SQL Agent log: SQLServerAgent Error: 32.

Today the maintenance job that recycles the SQL Agent error log failed on one of our production SQL Server with the following error message:

Message: Executed as user: MyDomain\_svcSQLServer. SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022). The step failed.

This job uses [dbo].[sp_cycle_agent_errorlog] procedure that resides inside msdb, which enables… Read more

2 comments, 2,322 reads

Posted in Basit's SQL Server Tips on 6 June 2012

Tips to avoid deadlocks?

A database server should be able to service requests from a large number of concurrent users. When a database server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict… Read more

5 comments, 2,848 reads

Posted in Basit's SQL Server Tips on 4 June 2012

SQL Server 2008 Security Best Practices

If you are working as a DBA for a financial organisation where sensitive financial data is stored in your databases then security is usually your immediate concern. Securing SQL Server is not a easy task and you must follow a series of best practices in order to accomplish this task.…

Read more

0 comments, 12,959 reads

Posted in Basit's SQL Server Tips on 1 June 2012