September 17, 2014 at 5:27 am
I have the Ola Hallengren script for index maintenance. To test its functionality, I need to increase my fragmenation of few test tables. How can it be done?
September 17, 2014 at 5:29 am
Is this a development database on a dev server?
September 17, 2014 at 5:38 am
ofcourse it is.
September 17, 2014 at 5:39 am
Then you can shrink the database, this will severely fragment all the indexes.
September 17, 2014 at 5:44 am
Thts the first thing i did. It didnt turn out the way i wanted. Index fragmentatin did not increase.
I have narrowed down some tables to defrag operation. Their index fragmentaion is very less which makes it difficulet to test the script.
September 17, 2014 at 5:49 am
To test the script, index frag has to increase. Only then the script process can be fully understood,.
September 17, 2014 at 6:02 am
How big are the tables?
September 17, 2014 at 6:10 am
Some are Big tables, has more than a million rows
September 17, 2014 at 6:28 am
Have you tried deleting a large set and then inserting more records (with random PK values)?
September 17, 2014 at 7:39 am
yes.. added few 1000 rows and ran deletes too. Fragmentation increased by 0.001409
September 17, 2014 at 7:50 am
OK, make sure that you are deleting random rows (not all in one block).
Also insert randomly as well.
Failing that, you could create a new table with a GUID as a PK and then insert records:-
CREATE TABLE dbo.[FragTest]
(ID UNIQUEIDENTIFIER PRIMARY KEY,
NAME CHAR(4))
INSERT INTO dbo.[FragTest]
SELECT NEWID(), 'TEST'
GO 10000
I've run this on my local instance and it gave me 96.6% fragmentation. You may want to insert more records than I did for a complete test.
September 17, 2014 at 8:28 am
Hi
No doubt for the Ola Hallengren scripts.
But because you're curious, I'd recommend you to just shrink the database. It should introduce high fragmentation to database tables.
Igor Micev,My blog: www.igormicev.com
September 17, 2014 at 9:02 am
DBA From The Cold's script did the trick. it created table with above 90% frags.
What about LOB columns. Wont it have Fragmentation?
September 17, 2014 at 9:06 am
Igor,
Shrinked log file. It didnt frag the indexes in the tables i am testing the Ola Hallengren script.
I have to understand the script to better customise it for my needs.
September 17, 2014 at 9:09 am
balasach82 (9/17/2014)
Igor,Shrinked log file. It didnt frag the indexes in the tables i am testing the Ola Hallengren script.
I have to understand the script to better customise it for my needs.
Of course not. Shrinking the log file isn't going to fragment the index. You would need to shrink the database to fragment the index.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply