I am tasked with maintaining a large database still on SQL Server 7.0.
Performance is an issue not surprisingly and I've targeted two rather
large tables to see what I can do with the indexes.
Now MatterConflicts row count is approaching 500K and
MatterConflictHits is approaching 1 Million rows. There are only one
index on each table, each for the table's primary key. The Matters
field in MatterConflicts table joins back with a table that users
access directly.
Question is, would it be beneficial to add, or modify the existing
indexes for these tables to include both the primary and foreign keys,
as well as an additional field?
Doesn't seem to be to be very beneficial to have an index that only
includes the primary key. So for example I'm thinking of creating an
index for MatterConflicts that includes the fields: MatterConflicts,
Matters, and HitMatters.
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
Hi Has anyone heard of MS SQL Server 7 dropping indexes? I had created an index on a table. The next day the index had disappeared. Has anyone expirienced such a problem?
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this: When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004 Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing? If so does this "pseudocode" example also cause this to occur: sqlstring="select * from item where itemid=12345" rs.open sqlstring, etc, etc, etc rs.Fields("ItemName")="My New Item Name" rs.Fields("ItemPrice")=1.00 rs.Update Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
Dear All, i'm planning to drop all the non clustered indexes (as they are not congigured well) on production database, and run the latest script to create fresh non clustered indexes on specific columns.
now my doubts are 1)will the replication affect with dropping and recreating of indexes? 2)query to drop all the non clustered indexes on that database.... can i use the query delete from sysindexes where indid>1 will the query works for me to drop all the non clustered indexes? 3)is it necessary to generate a snapshot again after creating the new indexes? or can i drop and run at subscriber also?
please guide me in this regard
Arnav Even you learn 1%, Learn it with 100% confidence.
Our inhouse app used to run on a SQL2000, but we've recently moved it to 2005. The move was done by way of backup and restore (it was on a whole new server).
After the move, an odd problem showed up: once in a while, the server seems incapable of finding/using its indexes: everything starts working slowly, until I run a maintenance plan that rebuilds its indexes etc. In the database/server all relevant options seem to be ok (auto update statictiscs etc.), and my conclusion that it doesn't use its indexes comes from the fact that: * it gives the results from certain select statements in a totally different order (although the set of rows is the same), * performance is (all of a sudden) very slow (seconds turning to minutes!!)
This happens: * at least after a reboot of the server * sometimes just in the middle of the day
The only way I've found to solve the matter, is by running the maintenance plan to rebuild the indexes, but sometimes this only seems to work the second time.
Does anybody share this experience, or know what to do about it?
hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3) drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table. is there a way to check if a a table contains data or not, The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing... regards
on which the following query is based. I need to build indexes so that the query will perform better. Now its very slow..
SELECT DISTINCT C.[afflt_cust_natl_key],[as_of_dt] FROM [dbo].[SF_Affiliate_Customer] C WHERE ( [afflt_intrnl_cust_ind] = 'N' AND [afflt_empl_ind] = 'N' AND (ISNULL([phys_addr_st_rgn_cd],'')<>'CA' AND ISNULL([mlng_addr_st_rgn_cd],'')<>'CA') )AND
I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .
TABLE( Column1 int FLAG1 bit FLAG2 bit )
Sample queries :
Select * from TABLE where FLAG1 =1 and FLAG2 =0 (Any combination of these flags) Select * from TABLE inner join SOMEOTHERTABLE on TABLE.Column1 = SOMEOTHERTABLE .Column1 where FLAG1 =1 and FLAG2 =0
( any join and combination of flags)
Questions :
What would be the best nonclustered index strategy :
Column1 as the index key including FLAG1 and FLAG2 or Column1,FLAG1 and FLAG2 in the index key
Points to note :
The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.
On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .
Does the predicate cause more overhead than reorganizing the index or is it the opposite ?
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked. 2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
It's often said or done that when inserting or updating into a 'large' table that disabling the non-clustered indexes can is needed for performance.
Now I know the obvious way to find out if this is best or not is by testing the different options. I was wondering if there was a rule of thumb to this?
Say you have a table with half a billion rows and 4 non-clustered indexes and are only updating half a million rows then sometimes disabling every night and re-enabling can take way more time than the actual update. Haven't found an articles advising to disable them when a table is over X rows and you are updating Y% of them...
I'm just beginning to experiment with memory optimised tables.
I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.
I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.
(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)
The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
I have inherited a database that is over-indexed, i.e. there are sometimes 10-20 indexes on a table. The performance is at times not great due to blocking from long running queries. I want to clean up the indexes as a starting point.
Through a query I found some time ago on the SQLCat blog I have discovered a large number of indexes in the database that have a huge disparity between reads and writes. The range of difference is sometimes almost 2 million more writes than reads. Should I just drop the indexes that have say, more than 100,000 more writes than reads and then see what the Missing Index DMVs tell me after a few days of running without those indexes?
In some cases there are a few hundred thousand reads but maybe a million writes on the index. Thus, there are a fair number of reads happening, just not in comparison to the number of writes. In some cases there are almost no reads and a million or more writes. I am obviously dropping those indexes. I just am not sure what to do about the indexes that do have a fair number of reads.
My index reorganise maintenance plan fails partly due to the disabled indexes
Executing the query "ALTER INDEX [I_ModelSecurityCommon_RECID] ON [dbo]...
" failed with the following error: "Cannot perform the specified operation on disabled index 'I_ModelSecurityCommon_RECID' on table 'dbo. Model SecurityCommon'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I don't want to delete the indexes as they are standard indexes that where on the DB from install.. any script that will reorganise all enabled indexes? and also to rebuild?
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK] AS DECLARE @sql NVARCHAR(max) DECLARE @DB VARCHAR(max) DECLARE databasecursor CURSOR FOR
