How to check DB performance after fragmentation

Question

0.00/5 (No votes)

See more:

I gone through in net to improve performance we need to treat fragmentation in particular database

I came to know we can alter index in two way REBUILD(when fragmentation s greater than 30 % ) and reorganize (when fragmentation is less then 30 %)

So i did rebuild and reorganize according to the fragmentation percentages .and also i noticed while doing fragmentation percentage is getting lower when page count is above 1000 like that for particular index and also i targeted fragmentation for CLUSTERED INDEX type

But after all this i checked DB size its same still.. Could you please help me how could i can able to check performance of the DB from front end

Posted 3-Mar-13 20:47pm

kimberly wind

Updated 3-Mar-13 21:09pm

Mehdi Gholam

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-04T01:25:00

Solution 1

To reduce database size, use: SHRINKDATABASE[^] or/and SHRINKFILE[^]

MS SQL Server performance is very challenging subject. I don't know how to check it from front-end at all, but...
One way is to measure the time of query execution, using: SET STATISTICS TIME[^] command.
Second way is to check disk activity, using SET STATISTICS IO[^].

More about SQL Server Performance Tuning, you'll find here: http://blog.sqlauthority.com/sql-server-performance-tuning/[^].

SQL Server performance depends of query/queries performance. If you want to find the longest running query, read this: http://blog.sqlauthority.com/2009/01/02/sql-server-2008-2005-find-longest-running-query-tsql/[^]

Posted 4-Mar-13 1:25am

Maciej Los

Comments

kimberly wind 4-Mar-13 23:26pm

HI this everything about i have seen already and i did defragmentation but question is how to check performance ?>?

Maciej Los 5-Mar-13 6:17am

I don't get it... You ask about performance of MS SQL database. As i wrote, performance of database depends on many things, it's very challenging subject. I've showed you method to check performance of MS SQL database: time of queries execution, disk usage, etc. What do you expect more? What do you really mean: "database performance". You should know that the performance of database depends on hardware too.

BTW: 1 star for my answer is unfair. But this is only my opinion.

kimberly wind 5-Mar-13 6:48am

Hi ,
Your write in a way

My 5

Maciej Los 5-Mar-13 7:14am

Thank you ;)

Davidduraisamy · Answer 2 · 2013-03-04T01:36:00

Solution 2

To check performance use this query

SQL

SELECT avg_fragmentation_in_percent, fragment_count
 FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED')
 GO

For more clarification check this link:http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/[^]

Posted 4-Mar-13 1:36am

Davidduraisamy

Comments

Maciej Los 4-Mar-13 7:42am

Good link, my 5!

kimberly wind 4-Mar-13 23:25pm

i have defragmentation but question is how to check performance ??????????/

kimberly wind 4-Mar-13 23:27pm

i wants to test from front how performance ????