SQLServer-QA.net - Knowledge Sharing Network (SSQA.net) : indexes

How fast you can obtain information about missing indexes? Its easy and quick!

Mon, 23 Nov 2009 00:26:00 GMT

On the subject line the process to find the answer is not a short one! By design SQL Server engine takes care when the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes...(read more)

TSQL tip on how much is the usage of database indexes?

Sun, 26 Jul 2009 22:43:00 GMT

Recently there hasn't been much of SQL Server tasks for me, as most of the time I was involved on designing the solutions for larger installations - a 8 node cluster or VLDB configuration etc. Ok I admit that still it can be classified as SQL Server...(read more)

SQL Server Deadlock and Trace Flag 1204 and 1222- a big question every time

Tue, 17 Mar 2009 07:03:00 GMT

I have seen few users out there thinks that Deadlocks in SQL Server is a bug which has not be corrected by Microsoft since its release! Even few times they get think that deadlocks and blocking are similar reactions in SQL Server, a typical situation...(read more)

Indexes and UPDATE STATISTICS differences and undocumented options - SQL Server 2005

Fri, 16 Jan 2009 07:32:00 GMT

During an Usergroup meeting one user asked the question that why my query is affected with slow performance when the statistics are upto-date? When asked about the indexes the reply was why do you need indexes when you have updated statistics on all the...(read more)

SQL Server Performance - consideration on unique index or filtered index.

SqlServer-QA.net — Thu, 25 Sep 2008 07:46:00 GMT

It is no doubt that when a database consists ' well-designed ' indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. You might be wondering I'm going back to basics for indexes! So...(read more)

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?

SSQA.net — Fri, 25 Apr 2008 08:30:00 GMT

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases...(read more)

SQL Server 2008 Spatial data - geography & geometry, all in one

SSQA.net — Thu, 20 Mar 2008 08:32:00 GMT

Geography & Geometry has much significance in day to day life, within that as a data now you can manage within your Database using SQL Server 2008 new data type - spatial, this represents information about the physical location and shape of geometric...(read more)

SQL Server 2005 Partitioned Tables and Indexes - learning curve

SSQA.net — Wed, 12 Mar 2008 08:01:00 GMT

When do you need data partitioning? The data you see in relational database may need such a strategy if the volume is huge, for instance the data to store event-log information having the schema such as: [Id] INT (make this primary key clustered), [Unit_Id...(read more)

How long DBCC CHECKDB process runs on SQL Server instance?

SSQA.net — Thu, 14 Feb 2008 11:43:00 GMT

In order to keepup the database logical and physical integrity of all the objects, you must run the DBCC statement such as DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG. Overall you can do this with another statement DBCC CHECKDB that will avoid...(read more)

SQL Server 2005 DMV - how it can help to consider whether index is useful or not?

SSQA.net — Mon, 04 Feb 2008 14:06:00 GMT

When it comes to performance, for a DBA indexes are the first one to come to mind in order to fine tune the tasks on the database. In this series we have already covered this huge topic of indexing and how best you can make use of Dynamic Management Views...(read more)

SQL Server 2005 TSQL to obtain buffers by object (table, index) in the buffer cache?

SSQA.net — Fri, 01 Feb 2008 12:30:00 GMT

As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do...(read more)

How SQL Server manages when a database is created in terms of I/O and disk usage?

SSQA.net — Tue, 29 Jan 2008 11:46:00 GMT

One of the best features you have in the SQL Server is to create database data file (additional) on fly without having a slow performance affect on existing connections. But think about how SQL Server manages to use server threads for the data file that...(read more)

SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!

SSQA.net — Fri, 25 Jan 2008 09:41:00 GMT

Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database. Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation...(read more)

SQL Server indexing - when to reorganize or rebuild?

SSQA.net — Mon, 21 Jan 2008 10:30:00 GMT

Re-organizing or rebuilding index would help the SQL optimizer to reduce the fragmentation for a better performance. In this case few users might have doubt that when to rebuild or reorganize (SQL Server 2005)? IN the series of Performance blog posts...(read more)

SQL Server Query Performance - think about page split and fragmentation, measures to reduce the behaviour and best use of dm_db_index_physical_stats DMV

SSQA.net — Mon, 14 Jan 2008 08:22:00 GMT

It is an universal truth in the database field that due to the fragmentation and page split the performance will be affected even for a simple [ SELECT <ColumnName> from.... ] type of query. So what you need to check or take action in terms of DBA...(read more)

Vote now: Index defragmentation best practices for SQL Server 2005 documentation - feedback received from Microsoft

SSQA.net — Fri, 04 Jan 2008 23:35:00 GMT

May 25 2007- From the snippet of Tibor Karaszi , SQL Server MVP I have raised a feedback form on Connect.Microsoft website to get newer version (SQL Server 2005) about Index Defragmentation Best practices information. Vote your interest to get newer documentation...(read more)

SQL Server SP_HELPINDEX - updated version that includes column names

SSQA.net — Thu, 03 Jan 2008 19:46:00 GMT

Here is the incentive for the SQL Server users (this new year), you may be aware then using SP_HELPINDEX or SP_HELP could give some part of information on the indexes and database objects. But to know more about depedant columns you need to query INFORMATION_SCHEMA...(read more)

SQL Server ETL Performance during Data Load Optimization

SSQA.net — Thu, 20 Dec 2007 14:40:00 GMT

Do you have a large ETL process to finish on day-to-day basis? Is your ETL process is suffering with performance loss during the BULK insert or export task? Are you using SQL Server 2005? If it is YES for all the above then you can take advantage of SQL...(read more)

TSQL to findout blocking and locks on a SQL Server?

SSQA.net — Fri, 07 Dec 2007 19:08:00 GMT

Have you ever performed huge operations such as deleting records of a table and processing inserts on that table at the same time? This is a common task that every application will have to perform and you can avoid by fine tuning your queries (mostly...(read more)

Internal Tables in SQL Server 2005, what are they for?

SSQA.net — Fri, 02 Nov 2007 08:17:00 GMT

By default SQL Server automatically creates the internal tables for Full-text search, XML indexes and Service broker. These are also created when a user query is written poorly that uses tempdb heavily. At they are called internal tables but do not contain...(read more)