Non Clustered Effect

Hi ,
When I finish to create one non clustering index on of of my table , I
found out that the size of the hard disk increase amazingly.
I decide to drop the this index but the hard disk size did not back to
original size before that. What should I do next ? Please help
Travis Tan
Travis wrote:
> Hi ,
> When I finish to create one non clustering index on of of my table
> , I found out that the size of the hard disk increase amazingly.
> I decide to drop the this index but the hard disk size did not back
> to original size before that. What should I do next ? Please help
It's possible to configure automatic grow for database files and in this
way, when a db need space for an object (table or index) it take from os. If
you delete an object the space previously allocated won't be shrunk (unless
you set auto_shrink option but it's better to avoid this).
You may use DBCC SHRINKDATABASE or, better, DBCC SHRINKFILE to reduce the
space used by a database. See BOL for more details and for sintax of DBCC
commands.
Bye
PS: "clustering" in the name of this newsgroup means "Clustering technology"
and not as clustered (or not clustered) index...
Luca Bianchi
Microsoft MVP - SQL Server
http://mvp.support.microsoft.com

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-Dan
What's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>
|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.co m...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria