Database Fragmentation with Visual Defrag

Visual Defrag is a defragmentation tool, designed specifically for Microsoft SQL Server database administrators and developers to defragment their indexes.
Visual Defrag allows DBAs to view all the indexes in a database, see how heavily fragmented each index is, and choose whether to defragment one index, a selection of indexes or all indexes.

What is Database Fragmentation?
From time to time, depending on how your data is used, you will need to reorganize the indexes on your tables. Indexes don’t maintain their fill factor and become fragmented and user queries will suffer the effects of reduced performance.
The tables in your database become fragmented when data modifications are made, such as inserts, updates and deletes. These changes to your tables are not usually made equally among the rows, so the page fullness will vary according to the characteristics of the data, creating hot spots within your indexes.

Fragmentation happens when the logical order of the pages does not match the physical order of the pages, or when pages that are logically sequential are not located on the same extent. This means your indexes become less efficient and queries will take longer to return results to your users.

How Does Fragmentation Affect My Databases?
When you run queries that scan part or all of a table, if you have fragmentation on that table, it causes additional page reads, slowing down parallel data scanning and reducing the performance and speed of your database.
For example, for SQL Server to retrieve all the pages of a fragmented table in their logical order, the disk drive’s heads have to jump around on the physical disk, rather than performing just contiguous read-only operations. This ‘latency’ causes decreased performance on your database and frustration and complaints from your users. Using Visual Defrag to regularly remove fragmentation will make your databases run faster.

Why is Fragmentation a Problem?
When fragmentation is present, and particularly if the fragmentation is heavy, perhaps on a table with data that is constantly being modified, users will find the performance of the table is lower than it should be and queries will be slower.
With internal fragmentation, if SQL Server needs to insert a new row on a full page, it will allocate a new page and split the full page so that half the rows are on one page and the other half on the other page.

Splitting can be an expensive I/O operation and leads to external defragmentation because more often than not, the new page will not be stored contiguous with the original page which has been split. The longer SQL Server takes in splitting pages, or jumping around the disk to return query results, the longer your users must wait.

This is how Visual Defrag can help. By using the built-in scheduler you can plan defragmentation jobs to take place on heavily used tables as often as you need, and at times when users will not be affected.

What is Defragmentation?
User modifications, such as inserts and deletes on the data in your tables, create pages splits. Performance will be lost as it takes SQL Server longer to return results of queries as these splits mean your indexes become less effective. Defragmentation is the process of putting your table indexes back in order and removing the page splits, so queries run at the speed you would expect and your users are not kept waiting.
Defragmentation should be a major part of your database maintenance plan to make sure frequently modified tables are regularly defragmented to keep them running at optimum speed. Visual Defrag enables you to regularly defragment your database tables at times when your users are not using the database by creating scheduled defrag jobs. This keeps your databases performing at their optimum speed and your users will benefit from faster data access.

Source: http://www.norbtechnologies.com/products/visualdefrag/faqs-defrag-sql-server.aspx

About index defrag: http://blogs.digineer.com/blogs/larar/archive/2006/08/16/smart-index-defrag-reindex-for-a-consolidated-sql-server-2005-environment.aspx

http://sqlfool.com/2009/03/automated-index-defrag-script/

Best way to get identity of inserted row?

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it’s across scopes. You could get a value from a trigger, instead of your current statement.
Another way said: @@IDENTITY returns the id of the last thing that was inserted by your client’s connection to the database.
Most of the time this works fine, but sometimes a trigger will go and insert a new row that you don’t know about, and you’ll get the ID from this new row, instead of the one you want

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
Another way said: SCOPE_IDENTITY() solves this problem. It returns the id of the last thing that you inserted in the SQL code you sent to the database. If triggers go and create extra rows, they won’t cause the wrong value to get returned.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren’t quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.
Also said: IDENT_CURRENT returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you’ll get the ID of that row instead of your one.

If you want to play it safe, always use SCOPE_IDENTITY(). If you stick with @@IDENTITY and someone decides to add a trigger later on, all your code will break.

More information:

http://blog.beyondrelational.com/2009/03/sql-server-identity-columns.html

http://msdn.microsoft.com/en-us/library/ms175098.aspx