Well-designed indexes are an important part of database optimization because they are the key factor in achieving optimal query performance and response time. In the meantime, poorly designed indexes, such as missing or excessive indexes on tables, indexes placed on the wrong column or inadequate index maintenance strategies, can be… Read more

In many situations, data modification requires several steps. For example, you may need to change the values ??in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that… Read more

The well-designed online analytical processing (OLAP) solution is fundamental to organizations analysis capabilities. That is why, when designing the OLAP solution for your organization, you must ensure that the components of your OLAP solution, including relational data warehouse databases and multidimensional cubes, are designed to meet the business needs of… Read more

With the release of SQL Server 2005 and later releases, Microsoft introduced set of new dynamic management view and function that helps database administrators (DBA) and developers to identify potential index candidates based on query history.

Checkout the part-3 of my three part article series on index-related dynamic management views… Read more

The following table lists all builds of SQL Server 2012 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 2012 Builds Breakdown:

Read more

The following table lists all builds of SQL Server 2008 R2 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 2008 R2 Builds Breakdown:

Read more

The following table lists all builds of SQL Server 2008 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 2008 Builds Breakdown:

Read more

The following table lists all builds of SQL Server 2005 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 2005 Builds Breakdown:

Read more

The following table lists all builds of SQL Server 2000 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 2000 Builds Breakdown:

Read more

The following table lists all builds of SQL Server 7.0 known hotfixes, service packs and KB articles that have been released by Microsoft.

Quick Overview:

Microsoft SQL Server 7.0 Builds Breakdown:

Read more

Like other mainstream commercial database systems, Microsoft SQL Server allows you to create multiple indexes on the same column of a table. This increases the likelihood of having duplicate indexes in the database because SQL Server does not prevent you from creating duplicate indexes, infect we can create up to… Read more

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc.

Checkout my article (Tips for Minimizing Deadlocks) on SSWUG.org, in which I shared some tips that you can follow to minimize deadlocks in SQL Server online transaction processing (OLTP) and online analytical processing (OLAP)… Read more

Checkout the sixth part of my XML support and SQL Server article series here.

In this part, you will learn about XML indexes (primary and secondary), and how they can effectively improve xQuery performance. You will also learn how you can manage the XML indexes using SQL Server Management… Read more

In my previous article (Different techniques to identify blocking in SQL Server) on MSSQLTips.com, I discussed about locks and blocks, and presented you with an overview on how to troubleshoot and resolve blocks using dynamic management views and Activity Monitor. After I wrote this article, I… Read more

The public role is a special fixed-database role, which exists in every SQL Server database. The public role is different from all other database-level roles because in SQL Server, every database user is automatically assigned to the public database role, and you cannot remove public database role from SQL Server… Read more

SQL Server uses indexes to sort and organize table data. It creates indexes based on ordering values from one or more selected columns. SQL Server automatically creates indexes when you define a primary key or unique constraint. You can use indexes to manage and track rows in a table or… Read more

I received an email from friend today asking how he can see when statistics were last updated in SQL Server. Well, in SQL Server, there are two ways to access the last modified date of a statistic, which are:

1. Through the header information using DBCC SHOW_STATISTICS.
2. Through STATS_DATE() function…

Read more

SQL Server stores all informational messages, warning messages, and errors in operating system and application log files. As a database administrator (DBA), it is our responsibility to review the information in the error log files on a daily basis for any errors that have occurred in our SQL Server environment.… Read more

Microsoft SQL Server provides DBAs and Developers with several index related dynamic management views and functions, which they can use to maintain, modify, tune and identify missing indexes. Some of these dynamic management views (DMVs) and functions (DMFs) you might use are listed as follow:

• sys.dm_db_index_operational_stats — Returns information about…

Read more

As per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role. This is because public role is a special database role that exists in every user database, and… Read more