About statement- LOP DELETE SPLIT

nikeman77

hi,
Anyone knows what does below statement do? will it change the data inside database?

taken from:
[http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e64f6f30-fd62-4ac4-b8bf-bef98b85ecbe/how-do-i-view-the-transaction-log-in-sql-server-2008]

---

SELECT


*

FROM



::fn_dblog(NULL, NULL)

WHERE



operation = 'LOP_DELETE_SPLIT'

Marije_Brummel

No.

This searches for page splits

http://www.sqlskills.com/blogs/paul/tra ... ction-log/

nikeman77

Hi Mark,

thanks for the link.

Purpose:

how to track page splits proactively

About SPLIT:

A ‘nasty’ split is what we think of a just a page split – a data or index page having to split into two pages to make space for a record to be inserted or an existing record to expand. A ‘good’ split is what the Storage Engine calls adding a page on the right-hand side of the index leaf level as part of inserting new records in an ascending key index (e.g. a clustered index with a bigint identity column as the cluster key).

Qs:
What's index fragmentation? does it works like what windows operating system fragmentation does ?
eg: for improving performance that we need to run especially occasionally when a new program is installed or removed FAT Sys memory is being 'adjusted'. So a fragmentation needs to be run once in a while to improve the performance.

Basically I'm quite an idiot (sad to say but its true i must admit it) to SQL. ](*,)

DenSter

Don't call yourself an idiot, you're just learning. You'd be an idiot if you didn't get the help you need

You got it on the fragmentation part, it works very similar to file fragmentation. SQL Server likes it when indexes are nicely organized. It puts the data of the indexes in pages, and when one of those pages fills up, it splits it into two pages. When this happens a lot, the index will fragment. Not only does it take time to split those pages, but the more split they are, the longer it takes to find data.

The database must be set up with proper fill factor. This means that when the indexes are rebuilt, SQL Server will make it so that there is enough room to insert new data. For instance, if you set the fill factor to 80%, each page will have 20% of space to grow. This is also why you need to have a job in SQL Server to rebuild indexes regularly, so that it can clean up the data on a regular basis.

Check out this video on basic database maintenance: http://youtu.be/0KbZkKdyZps
I also have one on setup: http://youtu.be/pOIjde-xpz4