hello everyone,
we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?
Can anyone let me now why the extent scan fragmentation is very high.
I do have a clustered index on this table .
The fill factor is 0 and this table has high inserts as it is used to maintain history.
Rebuilding the Indexes did not help.
DBCC SHOWCONTIG scanning 'ACCOUNTS' table...
Table: 'ACCOUNTS'(1061578820); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 728157
- Extents Scanned..............................: 91759
- Extent Switches..............................: 93305
- Avg. Pages per Extent........................: 7.9
- Scan Density .......: 97.55% [91020:93306]
- Logical Scan Fragmentation ..................: 0.33%
- Extent Scan Fragmentation ...................: 99.99%
- Avg. Bytes Free per Page.....................: 76.6
- Avg. Page Density (full).....................: 99.05%
DBCC execution completed. If DBCC printed error messages, contact your system administrator
Thanks in advance,
Shades[B]Extent Scan Fragmentation
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers?? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s??
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
I have a non-clustered index on a table. If I rebuild or reorganize it in SQL 2005, the total fragmentation percent reported by properties/fragmentation on the index stays at 33%.
Why doesn't the fragmentation go to 0% ?
If I totally drop/create the index, starts even higher, but beorg or rebuild simply goes to 33%. This even if using with use temp db for sort option.
On some tables when I run DBCC ShowContig followed by DBReindex followed by ShowContig I notice Extent Scan Fragmentation actually increases. Why does this happen? Below are the SHOWCONTIG results after running DBReindex three times.
After First DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 47.58%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
After Second DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 20.16%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
After Third DBReindex
- Pages Scanned................................: 986
- Extents Scanned..............................: 124
- Extent Switches..............................: 123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [124:124]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 67.74%
- Avg. Bytes Free per Page.....................: 91.0
- Avg. Page Density (full).....................: 98.88%
Thanks, Dave
Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.
Thanks!
If I rebuild the clustered index using this option (Drop Existing) without specifying the Fill Factor, would it it retain the original Fill Factor?
Thank you
I need a way to push create index statements drop to the subscribers. If I can use replication to distribute index creation that would be great. We don't allow our clients to make any schema changes directly. We are using transaction and merge replication.
View 4 Replies View RelatedWhat functions of tools do you use for managing index fragmentation?
DBCC?
I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.
Does anybody use this function?
Thanks
before rebuild index
---------------------
tablenameIndexname
avg_fragmentation_in_percent
Payoff_QuotePK_Payoff_QuoteDetail
83.3333333333333
ALTER INDEX ALL ON Payoff_Quote REBUILD
after issue the above statement result remain same.
Hi
When I run sys.dm_db_index_physical_stats on AdventureWorks.Production.Product, I get an average fragmentaion of 46%, 0%, 25% & 50% on the Indexes.
I run ALTER INDEX ALL ON AdventureWorks.Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
and the index fragmentation does not change...
Why is that?? I have tried refreshing the table and the statistics but why does it look like rebuilding the indexes has done nothing?
I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.
What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.
Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?
- Eric
We have nightly job running to reorg all in one of our prod database. But the index on one of the table fragmenting quickly by the morning showing 90% fragmentation.
View 9 Replies View Related
Hi,
It is natural that index gets fragment overtime. But I would like to know how you do the reindexing or defragment when your database table is big and you cannot afford the time to rebuild them. Thanks.
I have several databases in which our indexes have not been rebuilt/reorganized. I have worked primarily on SQL 2005 but with 2000 I am not familiar with Logical vs Extent fragmentation. And on the 2000 server there is high Logical Fragmentation (1000+%). With me rebuilding the indexes on the 2000 server databases, will this reduce performance or halt functionality of the servers while this process is running???
View 6 Replies View RelatedWe have few tables where we do truncate load or only do insert activities , why do the cluster index get fragmented very often to > 80%?
View 9 Replies View RelatedI need to manage the problem of negative performance implications when I fragment a 1TB+ DB. I want to perform Index Reorganization if fragmentation is no higher than 30%, and Index Rebuild if the fragmentation exceeds 30%.
Firstly can anyone recommend a script which uses sys.dm_db_index_physical_stats system to ascertain the
fragmentation level. Secondly, is there a technique I can employ to prevent the ONLINE operation completely killing performance on 27/4 production system?
ALTER INDEX REORGANIZE/REBUILD WITH (ONLINE=ON)
I have been testing methods to maintain indexes in a SQL Server 2005 database which has been migrated from SQL Server 2000. The compatibility level is still set to 80. I used the query below to inspect the degree of fragmentation amongst other things.
SELECT a.index_id
, name
, database_id
, avg_fragmentation_in_percent
,index_type_desc
,fragment_count
,page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Some of the indexes in the database had a high degree of fragmentation based on the avg_fragmentation_in_percent value. I tried drop+create, rebuild and reorganise commands on those indexes. Predictably, drop + create was the most effective, but even that did not always reduce fragmentation much. Sometimes the fragmentation was the same no matter what method I used. Other times drop+create helped, rebuild made it worse.
What is going on?
I have a table that has a clustered index that is only the identity column on the table. The table is somewhere around 200K rows and has 3800 pages in the index. We run our index maintenance every other day on this database using Ola's scripts and this index is rebuilt because it is 40-60% fragmented after 2 days. Overall, this isn't really too much of a problem since the index rebuild doesn't take too long, but I am puzzled as to how this index is getting fragmented since the only column in it is the identity.
CREATE TABLE [dbo].[Example](
[ExampleID] [int] IDENTITY(1,1) NOT NULL,
[ExampleCode] [varchar](10) NOT NULL,
[ForeignID] [int] NOT NULL,
[AnotherID] [int] NOT NULL,
[code]...
) ON [PRIMARY]There is nothing strange like updates to the identity happening and while some records are deleted, there has only been about 20,000 in the life of the table (months). Not enough to account for the level of fragmentation that we're seeing on the index.About the only thing I can think of that would cause fragmentation on this index in this scenario are:
1. Page splits caused by starting with a small value in one of the VARCHARs and later inserting a larger value
2. Page splits caused by the NULLABLE column, ExampleDate, starting with NULLs and later updating them to a date.
For #1, I had development check the update scenarios for the varchar columns, especially the varchar(1000) one, and they didn't see it as a common thing where the values would go from small (or empty) to large.
For #2, I checked and found that the only value for that column in the table is NULL so while it always starts as NULL, it never gets updated to anything else.
I've tried looking at sys.dm_db_index_operational_stats and the leaf_update_count is around 300,000, but unless those updates are causing page splits, I don't see how they would contribute to fragmentation.
I have been reworking my index maintenance jobs from my old SQL 2000 table and view references to the DMV's and System Tables in SQL 2005, and I noted that some of my indexes end up being more fragmented after a reorganization and or rebuild. That doesn't make much sense to me at all. The code I am executing is:
Code Block
print ' '
print '************* Beginning Index Updates for '+db_name()+' *************'
print ' '
DECLARE @tablename varchar(250),
@indexname varchar(250),
@fragpcnt decimal(18,1),
@indexid int,
@dbID int
-- Determine DB ID
SELECT @dbID = DB_ID()
DECLARE tnames_cursor CURSOR FOR
SELECT b.name, c.name, a.avg_fragmentation_in_percent, a.index_id
FROM sys.dm_db_index_physical_stats (@dbID, NULL, NULL, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
WHERE a.index_id > 0
ORDER by a.page_count DESC
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid
WHILE (@@fetch_status = 0)
BEGIN
-- Declare and determine the tablename ID
declare @tablenameID int
select @tablenameID = object_id(@tablename)
IF @fragpcnt > 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REBUILD')
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was rebuilt.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
PRINT 'Post Rebuild fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
ELSE IF @fragpcnt BETWEEN 5 AND 30
BEGIN
EXEC('ALTER INDEX ['+@indexname+'] ON ['+@tablename+'] REORGANIZE')
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was Reorganized.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
SELECT @fragpcnt = avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@dbID, @tablenameID, @indexid, NULL, NULL) a
JOIN sys.indexes b ON a.object_id = b.object_id
AND a.index_id = b.index_id
JOIN Sys.objects c ON b.object_id = c.object_id
PRINT 'Post Reorganization fragmentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
ELSE
BEGIN
PRINT '***************************************************'
PRINT 'Index '+@indexname+' was left alone.'
PRINT 'Original framentation Percent: ' + convert(varchar, @fragpcnt) + '%'
PRINT ''
END
FETCH NEXT FROM tnames_cursor INTO @indexname, @tablename, @fragpcnt, @indexid
END
print ' '
print '************* NO MORE TABLES TO INDEX *************'
PRINT 'All indexes for the '+db_name()+' database have been updated.'
print ' '
DEALLOCATE tnames_cursor
Below are some snipits of the output:
***************************************************
Index _dta_index_wuci_history_8_1123587141__K2_K5 was rebuilt.
Original framentation Percent: 58.3%
Post Rebuild fragmentation Percent: 58.3%
***************************************************
Index PK__batchjob__776C5C84 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index PK__ContactWebDetail__116A8EFB was rebuilt.
Original framentation Percent: 44.4%
Post Rebuild fragmentation Percent: 77.8%
***************************************************
Index PK__managed_object_s__5DCAEF64 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index kb_IX_kb_scope_scope_role was rebuilt.
Original framentation Percent: 75.0%
Post Rebuild fragmentation Percent: 87.5%
***************************************************
Index PK__query__09A971A2 was left alone.
Original framentation Percent: 0.0%
***************************************************
Index PK__email_message__38996AB5 was rebuilt.
Original framentation Percent: 85.7%
Post Rebuild fragmentation Percent: 0.0%
***************************************************
If the index begins with PK, then it is the primary key index which is generally the clustered index on the table, but not always. If it has an IX on it, it is generally a non-clustered index on the table, but again not always. In the case of the above, the PK is a clustered index, and the IX is a non-clustered index.
Anyone have any ideas why this is functioning in this manner?
Thanks,
Jon
In my database all columns have Identity Value as a PK.
Now today I check Index Fragmentation I saw that many cluster and Non cluster Index are avg.Fragmentation is around 99 % I thought that in Identity column record is always inserted at bottom so there is no fill factor assigned to it.
So in this case Do I need to set Fill factor for Cluster and Non Cluster Index?
If Yes Then For PK How much - 95 % or what? and same for Non cluster or It should around 85 to 90
I am using sys.dm_db_index_physical_stats to identify indexes that need to be rebuilt based on a fragmentation limit. Once identified, I execute and ALTER INDEX... REBUILD on the index. If the index is clustered, only that index gets rebuilt for the table. After all the indexes are complete, I receive a report on the indexes that were rebuilt in the databases and what level of fragmentation the index was at before rebuilt. After checking these indexes, I still see that all the Primary Key indexes are still at the same fragmentation level. I run the process again and it does not change. I updated table usage and also ran update statistics after running the rebuild again, but the fragmentation does not change. Why can€™t these PK Clustered indexes be rebuilt as expected? Do I need to drop and recreate the PK before this fragmentation changes?
View 7 Replies View RelatedI've noticed that indexes in my AX 2012 database get very fragmented very quickly. Is this normal and that I should just defragment them regularly or is there a possible issue here that needs investigated?
View 6 Replies View RelatedI thought I would delve into index fragmentation and I found somegreat sql from many posters (thanks Erland!).My question is how bad is bad? I know this is very subjective.Some scripts I found would reindex if the LogicalFragmenation is over30%.I have some tables that are 98% (I'm guessing really bad). I know itall depends..more as a learning point: I found a table that had over 30%logicalfragmentation, I dropped the indexes, created then ran thescript that used type code segment:'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,NO_INFOMSGS')In one case, the indexes for the table dropped below 30%, in anothercase the index was still fragmented ever after I dropped and re-created index.SQL Server 2005 x64 SP2This is the script I am running (I found this in another thread thatErland posted):SET NOCOUNT ONUSE ds_v6_sourceDECLARE @TableName VARCHAR(100)-- Create a table to hold the results of DBCC SHOWCONTIGIF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULLDROP TABLE #ContigCREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,[IndexName]VARCHAR(200),[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,[MinimumRecordSize] INT,[MaximumRecordSize] INT , [AverageRecordSize] INT,[ForwardedRecords] INT ,[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]NUMERIC(6,2),[AveragePageDensity] NUMERIC(6,2), [ScanDensity]NUMERIC(6,2) ,[BestCount] INT ,[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,[ExtentFragmentation] NUMERIC(6,2) )DECLARE curTables CURSOR STATIC LOCALFORSELECT Table_NameFROM Information_Schema.TablesWHERE Table_Type = 'BASE TABLE'OPEN curTablesFETCH NEXT FROM curTables INTO @TableNameSET @TableName = RTRIM(@TableName)WHILE @@FETCH_STATUS = 0BEGININSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITHTABLERESULTS, ALL_INDEXES, NO_INFOMSGS')FETCH NEXT FROM curTables INTO @TableNameENDCLOSE curTablesDEALLOCATE curTables
View 8 Replies View RelatedWe have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
I have a situation where I need to rebuild indexes on a large DB (500G).
When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.
When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.
What can I do without removing my DB from H/A to rebuild the indexes?
Here is my code,
1. create table t1 (id int )
2. create unique index u on t1(id)
3. create table t2 (id int constraint f foreign key references t1(id))
4. ALTER TABLE t2 NOCHECK CONSTRAINT ALL
5. ALTER TABLE t1 NOCHECK CONSTRAINT ALL
6. drop index t1.u
I get a FK voilation exception. on line 6.
I was not expecting this, because I am doing 4 & 5
Is dropping the FK is only option?
Actually I am trying to move the unique index to a different file group.
------------------------
I think, therefore I am - Rene Descartes
Hi,
I am trying to drop an index usiong enterprise manager.The process hangs and Enterprise manager is not responding.When I checked the Current activity the process is in sleeping condition.I appreciate if any one can tell me what could be the reason for the process to be hung.
thanks
Mohan
Hi,
I have a table which I am unable to drop the index.
I tried to drop it via the TSQL and GUI interface (Indexes dialog box),
but when I clicked on table refresh, the index still there.
Below is the result after DBCC commands:
1. DBCC checktable(TB_LOCLoan)
Checking TB_LOCLoan
The total number of data pages in this table is 146542.
Table has 1596232 data rows.
Msg 605, Level 21, State 1
Attempt to fetch logical page 462136 in database 'DM_LOCLoan' belongs to object '1744009244',
not to object 'TB_LOCLoan'.
2. DBCC newalloc(DM_LOCLoan)
TABLE: TB_LOCLoan OBJID = 832005995
INDID=0 FIRST=60168 ROOT=642778 DPAGES=146542 SORT=0
Data level: 0. 146542 Data Pages in 18393 extents.
INDID=5 FIRST=463752 ROOT=463739 DPAGES=5304 SORT=1
Msg 2525, Level 16, State 1
Table Corrupt: Object id wrong; tables: alloc page 463616 extent id=463752 l page#=463752
objid in ext=-832005995 (name = -832005995) objid in page=832005995 (name = TB_LOCLoan)
objid in sysindexes=832005995 (name = TB_LOCLoan)
TOTAL # of extents = 18393
3. From Error Msg 2525, I have tried the suggested action
use DM_LOCLoan
go
select indid, name, object_name(id)
from sysindexes
where id=832005995
and distribution=463752
go
This query does not return any row.
I am running out of clue of how to proceed! Anybody come across this problem before?
I am very eager to solve this problem as the table is hanging here, I can't even drop it to recreate
and continue data loading.
Thank you.
Regards,
Joo Pheng
According to sysindexes, I have a table with a primary key and an index. I'd like to drop the index. However, when I give the drop command, the message "Cannot drop the index 'eventlog._INDEX', because it does not exist in the system catalog." is returned.
I'm not sure what to look for. How do I remove the index?
I'm trying to drop indexes if they already exist then recreate them as needed, however, I'm getting an error if the index does not exist and I thought that's what the "IF Exists" statement was for.
syntax:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'idx_acct_no') DROP INDEX accounts.idx_acct_no
go
create index [idx_acct_no] ON [dbo].[accounts] ([acct_no])
go
Error when index does not exist:
Server: Msg 3703, Level 11, State 7, Line 1
Cannot drop the index 'accounts.idx_acct_no', because it does not exist in the system catalog.
Is there anyway to not get this error?
Hi all,
I come from MySQL.
In sqlserverce
I'm looking for an equivalent command..to
"DROP INDEX IF EXISTS inventory.idxItems"
or somthing similar so that I do not get an error or crash the C# app when I try to drop a nonexisting index..
also for droping a table.
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP