Browse by Tags

All Tags » sql server » performance » indexes (RSS)
SQL Server Performance - consideration on unique index or filtered index.
Published 25 September 8 2:46 AM | SqlServer-QA.net
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...
Filed under: , , , , , , ,
SQL Server 2005 Partitioned Tables and Indexes - learning curve
Published 12 March 8 3:1 AM | SSQA.net
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...
Filed under: , , , , , , , , , , ,
SQL Server 2005 DMV - how it can help to consider whether index is useful or not?
Published 4 February 8 8:6 AM | SSQA.net
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...
Filed under: , , , , , ,
How SQL Server manages when a database is created in terms of I/O and disk usage?
Published 29 January 8 5:46 AM | SSQA.net
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...
Filed under: , , , , , , , , , , , ,
SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!
Published 25 January 8 3:41 AM | SSQA.net
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...
Filed under: , , , , , , , , , , , , ,
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
Published 14 January 8 2:22 AM | SSQA.net
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...
Filed under: , , , , , , , , ,
SQL Server ETL Performance during Data Load Optimization
Published 20 December 7 8:40 AM | SSQA.net
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...
Filed under: , , , , , , , , ,
TSQL to findout blocking and locks on a SQL Server?
Published 7 December 7 1:8 PM | SSQA.net
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...
Filed under: , , , , , , , ,
SQL Server 2005 standard query tuning options - plan guide for performance testing how to?
Published 30 October 7 10:41 PM | SSQA.net
You may be aware that plan guides will help the optimizer to perform better and a general recommendation is to use the relevant USE PLAN query hint when the other standard query tuning options are failed. It is also necessary to test through the results...
Filed under: , , , , , , , , ,
Stored Procedures caught me with too many recomplies - way out to avoid it
Published 25 October 7 3:35 AM | SSQA.net
A common scenario in SQL Server environment that often application users complained about slow performance and same time you observe high CPU usage/spikes on the SQL Server instance, so better to follow the methods from HighCPU-whyitis blog and still...
Filed under: , , , , , , , ,
Update Statistics on huge tables - best practice
Published 23 October 7 6:40 AM | SSQA.net
If you have valid indexes and they are maintained with regular schedule of reindexing to address fragmentation, then you can go with UPDATE STATISTICS on the tables that has frequent updates, say once in 2 days during the quiet time on the databases....
Filed under: , , , , ,
Guidelines for enabling indexes and online index operations
Published 8 October 7 8:50 AM | SSQA.net
As a DBA you should now how locking works with tables and how best you can make use of indexing enhancements within SQL Server 2005 version. Until SQL 2000 there was a limited availability of documentation on the guidelines even in the Books online and...
Filed under: , , , , , , ,
Disk Space Requirements for Index DDL Operations
Published 28 September 7 8:38 AM | SSQA.net
Are you watching your disk space during the indexes operations where these database objects are stored? Recently I was stumped on a database that is only 5GB had reindexing process failure due to 10% of disk free space was available. For your information...
Filed under: , , , , , , , , , ,
Performance consideration or overhead having primary key with 3 GUID (Clustered Index)
Published 25 September 7 4:17 PM | SSQA.net
During a database design there has been an interesting discussion that will there be any performance addition in adding a second non-clustered index on a Globally Unique ID ( GUID ) column that has already a unique clustered index. IF needed you need...
Filed under: , , , , , , ,
DBCC EXTENTINFO - unused pages in these extents (undocumented)
Published 4 September 7 7:45 AM | SSQA.net
When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when...
Filed under: , , , , , , , , , ,
Msg: 2522 The index "<name>" (partition 1) on table "TableName" cannot be reorganized because page level locking is disabled.
Published 7 August 7 8:43 AM | SSQA.net
All of sudden the re-org of an index started failing and no error was displayed within that scheduled job we execute. After executing the code from Query Editior I was able to get the message as follows: Msg 2552, Level 16, State 1, Line 3 The index "<name>"...
Filed under: , , , , ,
How to gain Disk related performance with few simple steps
Published 30 July 7 9:24 AM | SSQA.net
Within your SQL Server environment there are few simple steps you can take up in order to gain performance to a level, for instance when configuring a SQL Server that will only contain a few gigabytes (GB) of data and not sustain heavy read nor write...
Filed under: , , , , , , , , ,
Unused indexes on a database, its good and bad
Published 27 July 7 8:51 AM | SSQA.net
You may be aware that the indexes on a SQL Server database take up space, so in case of unused indexes its obvious that they waste storage. It is easy to find the indexes using SP_HELPINDEX statement against a table, so to identify the unused indexes...
Filed under: , , , , ,
How do I find all the statistics and statistics columns on a specified object?
Published 16 July 7 7:46 AM | SSQA.net
USE <database_name>; GO SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id FROM sys.stats AS s INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id INNER JOIN sys.columns AS c ON...
Filed under: , , , , , ,
Does rebuilding a clustered index rebuild nonclustered indexes?
Published 25 June 7 3:45 PM | SSQA.net
Does rebuilding a clustered index rebuild nonclustered indexes? I have discussed this question with one of SQL Server MVP recently, eventually both were correct to the point. Lately I have found KhenBlog on the subject, interesting....( read more ) Read...
Filed under: , , , ,
More Posts Next page »