Viewing 15 posts - 18,106 through 18,120 (of 49,552 total)
Ok...
Index (Logical) Fragmentation (also sometimes called external fragmentation, though the term is used for other things too) is the result of inserts and updates (row-widening updates) that result in pages...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2012 at 4:20 am
Sure.
CREATE UNIQUE NONCLUSTERED INDEX idx_Projects_ProjectIDPrimary (project_id, primary)
WHERE Primary = 1
p.s. Primary is a reserved word and probably should not be used for a column name. Call it something like IsPrimary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 1:26 pm
What's your reason for partitioning? 15000 rows is a fairly small table, is massive growth expected?
By creating a nonclustered index only on the partition scheme, what you've done is left...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 1:23 pm
newbieuser (6/12/2012)
Does SQL Server rebuild/alter index of a table on its own?
No, never.
Has to be a manual execution or a job or other scheduled task that someone created.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 1:20 pm
Maybe take a read through these:
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 1:18 pm
In this case I probably wouldn't bother with CheckDB, since this is TempDB. But there is an underlying IO subsystem problem here that needs identifying.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 10:48 am
Sure, but it's going to take ages to write and debug (and you're pretty much on the right track with the code posted).
Compare how much the product costs with...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 10:43 am
There's a problem with your IO subsystem - it's returning old data (old versions of pages). I would suggest you get your storage vendor in to help you, it could...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 10:40 am
Please post new questions in a new thread. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 10:07 am
Parameter sniffing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 10:02 am
Could well be inappropriate cached plan.
Please post table definitions, index definitions and execution plan (of the slow one), as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 9:17 am
3. Deletes don't cause fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 8:56 am
Then you need to do some re-architecting. SP calls are not allowed in a function, data modifications are not allowed in a function, a function like that in a merge...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 8:00 am
Use a stored procedure instead of a function.
Functions like that (data-accessing scalar functions) are terrible for performance
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 7:53 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 7:41 am
Viewing 15 posts - 18,106 through 18,120 (of 49,552 total)