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] UNIQUEIDENTIFIER, [EventType_Id] UNIQUEIDENTIFIER...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
03-12-2008
Filed under: sql server, performance, indexes, data, backup, bi, database, statistics, partition, monitoring, strategy, data warehouse
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 within SQL Server 2005 version that was lacking...
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 is used for the databases and how big disk queue...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
01-29-2008
Filed under: sql server, performance, management, indexes, windows, i/o, sysmon, disk space, usage, resource, perflib, worker threads, task manager
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, in this regard review the blogs by SQLQuery...
Posted to
SQLServer-QA.net - Knowledge Sharing Network (SSQA.net)
(Weblog)
by
Anonymous
on
01-25-2008
Filed under: sql server, performance, indexes, cache, users, tsql, best practice, backups, create, online, monitoring, operating system, busy, creation
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 role, on the execution of queries ensure that columns...
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 2005 in speeding up the ETL process to perform...
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 deletes). As you are aware the inserts operations...
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 you would expect are not upto the mark, when using...
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 you have not seen no sign of resolution to the problem...
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. During the performance monitoring exercise you should...