May 1, 2015 at 12:34 am
I have 3rd party application I'm supporting which is experiencing performance issues. 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
Not sure where to look next to solve this one?
Any suggestions you may have would be greatly appreciated.
May 1, 2015 at 12:47 am
SQLDB101 (5/1/2015)
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?
First suggestion is to get the problematic environment up to the same patch level, then you can compare like for like. Another thought are the maintenance operations, are they identical?
😎
May 1, 2015 at 4:05 am
Is the data the same? If the distribution is different, that could lead to radical differences in the fragmentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2015 at 3:46 pm
I would [select object_name(id),* from sysindexes where object_name(id) = 'mytable'] or [select object_name(object_id),* from sys.indexes where object_name(object_id) = 'mytable'] to find the fillfactor.
completely script out all the indexes on the table so that we can see and compare each index, between ENV1 and ENV2, and see if the tables are clustered on exactly the same index, and that each index has exactly the same key in exactly the same sequence, and that the indexes have the same fill factor.
If a clustered index is different, you might se differing behaviour if the clustered index column gets changed in one environment but not the other.
Some instance may have default fill factor set at 100%, whereas another might have 90 or 80 set at a server level.
This will affect the default value if you do a create index without specifying a fillfactor.
Some instances might default a primary key to non-clustered when creating it via the GUI.
This might be a swing and a miss, but maybe fragmentation could be due to one environment having Read committed snapshot isolation set as default and the other does not have snapshot? Not sure if allowed snapshot together set isolation snapshot will cause versioning on the table or only on tempdb.
Check default isolation levels of both databases anyway, just to be sure that they are the same.
May 1, 2015 at 10:01 pm
SQLDB101 (5/1/2015)
I have 3rd party application I'm supporting which is experiencing performance issues. This application runs on a SQL Server 2008 R2 database.
Why are you even thinking of touching a 3rd party application? It's time to have a little talk with the 3rd party vendor. Explain to them how their competition would love it if they didn't fix their own performance problem.;-)
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2015 at 7:31 pm
Hi All,
I appreciate all the responses. Sorry for being slow in getting back to you.
Grant - The data is exactly the same. Each message is piped to both environments.
MadAdmin - I have attached the output of the 2 queries you have provided. As far as I can tell the indexes look the same. The default index fill factor is set at 0 for both environments at the server level. Also the isolation level is set to "read committed" in both databases.
Jeff - I like the suggestion and while it sounds logical unfortunately we are stuck with what we have. I did raise the issue with the vendor before trying this forum but I have been given no feedback whatsoever. Great vendor.....
May 6, 2015 at 8:23 pm
I figured as much. Vendors aren't so helpful.
Can we narrow down the type of performance problem that you're experiencing? For example, are the performance issues on SELECTs, INSERTs, UPDATEs, or DELETEs? Are they for singleton rows or batches of rows? Is it through a GUI or something else that you're noticing the performance issue? Have you been able to narrow it down to a piece of code and, if so, could you post it along with an actual execution plan?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2015 at 4:14 am
Try using this query.
It should bring back info like, table, indexname, indexkey together with included columns, seeks, scans, lookups, rowlockcounts, rowblock counts, rowblock duration, page lock counts etc all together in one query instead of having to compile all the info from various queries.
Using this info, I would suggest run once and put in excel, then order by total scans descending, page blocking descending, then row block descending to find the troublesome indexes.
Good info to have when making a case to vendors that are hard of hearing.
I would suggest also looking at each indexkey together with included columns to make sure that the entire index is the same when comparing the 2 environments.
DECLARE @dbid INT
SELECT @dbid = DB_ID()
SELECT OBJECT_NAME(S.object_id) TableName, COALESCE(i.name, 'HEAP') indexName,--objectname = OBJECT_NAME(s.object_id) ,
--indexname = i.name ,
IndexKey, CONVERT(SMALLDATETIME, STATS_DATE(S.object_id, i.index_id)) statsdate, Usage.[rowcount], fill_factor,
i.index_id--, partition_number
, Usage.seeks, Usage.scans, Usage.lookups, Usage.updates, S.leaf_insert_count inserts,
S.leaf_delete_count deletes, S.nonleaf_delete_count nonleafdel, Usage.MaxRowsScanned, row_lock_count RwLkCnt,
row_lock_wait_count RwLkWtCnt,
CAST (1.0 * row_lock_wait_in_ms / ( CASE WHEN row_lock_wait_count = 0 THEN 1
ELSE row_lock_wait_count
END ) AS NUMERIC(15, 2)) [average row block duration in ms],
page_lock_count,
page_lock_wait_count, page_lock_wait_in_ms,
CAST (1.0 * page_lock_wait_in_ms / ( CASE WHEN page_lock_wait_count = 0 THEN 1
ELSE page_lock_wait_count
END ) AS NUMERIC(15, 2)) [average page block duration in ms]
FROM sys.dm_db_index_operational_stats(@dbid, NULL, NULL, NULL) S
INNER JOIN sys.indexes i ON i.object_id = S.object_id AND
i.index_id = S.index_id
LEFT JOIN ( SELECT
object_id, index_id, name,
LEFT(subwindow.includeKey, LEN(subwindow.includeKey) - 1) AS 'IndexKey'
FROM ( SELECT
i.index_id, i.name, object_id,
( SELECT c.name + ',' AS [text()]
FROM sys.indexes i2
INNER JOIN sys.index_columns ic ON ic.object_id = i2.object_id AND
ic.index_id = i2.index_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND
c.column_id = ic.column_id
WHERE ic.is_included_column = 0 AND
i.object_id = i2.object_id AND
i.index_id = i2.index_id
ORDER BY i2.name
FOR
XML PATH('') ) + COALESCE(' include:' +
( SELECT c.name + ',' AS [text()]
FROM sys.indexes i2
INNER JOIN sys.index_columns ic ON ic.object_id = i2.object_id AND
ic.index_id = i2.index_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND
c.column_id = ic.column_id
WHERE ic.is_included_column = 1 AND
i.object_id = i2.object_id AND
i.index_id = i2.index_id
ORDER BY i2.name
FOR
XML PATH('') ), '') AS includeKey
FROM sys.indexes i --where object_name(i.object_id) = 'loginAuditTable'
) subwindow ) window ON window.object_id = i.object_id AND
window.index_id = i.index_id
INNER JOIN ( SELECT S.[object_id], S.index_id, sysi.rowcnt AS [rowcount], SUM(user_seeks) seeks,
SUM(user_scans) scans, SUM(user_lookups) lookups, SUM(user_updates) updates,
SUM(user_scans) * sysi.rowcnt AS 'MaxRowsScanned'
FROM sys.dm_db_index_usage_stats AS S ---Usage
INNER JOIN sysindexes AS sysi ---Indexes
ON sysi.id = S.[object_id] AND
sysi.indid = S.index_id
INNER JOIN sys.objects SO ON SO.object_id = sysi.id
WHERE OBJECTPROPERTY(S.[object_id], 'IsUserTable') = 1 AND
database_id = @dbid
--AND rowcnt > 1000000
GROUP BY S.[object_id], S.index_id, sysi.rowcnt ) Usage ON i.object_id = Usage.object_id AND
i.index_id = Usage.index_id
WHERE OBJECTPROPERTY(S.object_id, 'IsUserTable') = 1 AND
database_id = @dbid
--AND object_name(s.object_id) = 'MyTable'
ORDER BY 1 ASC, 3
May 7, 2015 at 4:31 am
I think I'd go back to Eirikur's suggestion and make sure everything is patched to the same level. That could absolutely lead to this type of behavior ... if there's something in the patches related to index page management.
I can't think of anything off the top of my head that would cause a difference in fragmentation between standard & enterprise, but it is possible that there is one.
If those two things are the only differences, then, by process of elimination, they may be the culprits.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2015 at 12:50 am
once again thank you for all your responses
we did improve our performance by reviewing top queries and adding in a new index to a completely different table. we've since passed the information on to the vendor in the hope they will incorporate this into their build.
my original question still remains though, as to why the indexes in 1 environment are fragmenting much quicker than the other for "TableA".
MadAdmin - Thanks for your query. I have run it in both environments and attached the output (MadAdmin Query Results 20150514.xls). It does show up a difference in the StatsDate. Environment-2 is 25/4/2015 - which is when we did the last index rebuild. Whereas Environment-1 is yesterday. Could this point to the difference in fragmentation?
Grant - We are in the process of getting the 2 environments equal starting with the upgrade to SP2. I just have to work through some red tape, hopefully soon.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply