Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
Message posted via http://www.sqlmonster.com
What do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQLMonster.c om...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.sqlmonster.com
|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.sqlmonster.com
|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQLMonster.c om...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.sqlmonster.com
Showing posts with label anindex. Show all posts
Showing posts with label anindex. Show all posts
Wednesday, March 28, 2012
Index restructuring
Is their a monitor counter or profiler trace that might tell me when an
index is being restructured during heavy write times?
Message posted via http://www.droptable.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQ
droptable.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.droptable.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.droptable.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQ
droptable.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.droptable.comsql
index is being restructured during heavy write times?
Message posted via http://www.droptable.comWhat do you mean by "restructured"? There are PerfMon counters for page
splits (AccessMethods:PageSplits/sec), is that what you're referring to?
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:b51b69db39ae463fb5aeaab744865dc4@.SQ
droptable.com...
> Is their a monitor counter or profiler trace that might tell me when an
> index is being restructured during heavy write times?
> --
> Message posted via http://www.droptable.com|||Yes, I believe so.
I have some application timeouts that occasionally happen and have ruled
out locking, blocking, deadlocks, log growth, data growth, cpu, and memory.
This table is heavy on the writes in comparison to the reads, with a
clustered primary key index with a fillfactor of 90%, and so I suspect the
index might be reaching a point where it is, for lack of a better term
"restructuring" due to running out of free space.
Message posted via http://www.droptable.com|||Have you looked at the checkpoints? If you are write intensive when the db
issues a checkpoint it will attempt to write a bunch of dirty pages to disk.
If your drives can not handle the sudden massive requests the other users
will wait in the disk queue until the checkpoint is done. You never
mentioned Disks in your list. Check out the various disk related counters
and the Checkpoint pages per sec in perfmon and see if these correlate tot
he timeouts.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:e0defe3ebbe3418787f6050043594063@.SQ
droptable.com...
> Yes, I believe so.
> I have some application timeouts that occasionally happen and have ruled
> out locking, blocking, deadlocks, log growth, data growth, cpu, and
> memory.
> This table is heavy on the writes in comparison to the reads, with a
> clustered primary key index with a fillfactor of 90%, and so I suspect the
> index might be reaching a point where it is, for lack of a better term
> "restructuring" due to running out of free space.
> --
> Message posted via http://www.droptable.comsql
Index rename
What's a SQL statement for renaming an existing index. I know how to add an
index at the time that the table is created, but how do you rename an
existing index.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Check out sp_rename in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d63becf3fe8@.uwe...
> What's a SQL statement for renaming an existing index. I know how to add
> an
> index at the time that the table is created, but how do you rename an
> existing index.
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> What's a SQL statement for renaming an existing index.
Books Online is your friend.
EXEC sp_rename 'tablename.indexname', 'newname', 'index'|||too bad there's not a search.
Aaron Bertrand [SQL Server MVP] wrote:
>Books Online is your friend.
>EXEC sp_rename 'tablename.indexname', 'newname', 'index'
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||To make sure that I don't rename something else, I'm hoping to verify that
the "table.indexname" is also not the name of a column. I want to get the
correct item to rename. Maybe that can't be done with SP_Rename. since it
takes a string, not an object_ID.
HockeyFan wrote:
>too bad there's not a search.
>
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> too bad there's not a search.
But there is. Also, I found the topic very quickly in the index by typing
'renaming indexes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d681f6f81ec@.uwe...
> too bad there's not a search.
> Aaron Bertrand [SQL Server MVP] wrote:
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> To make sure that I don't rename something else, I'm hoping to verify that
> the "table.indexname" is also not the name of a column.
That's what the third parameter is for. Notice I say 'index' and not
'column'...|||> too bad there's not a search.
? There sure is.|||There's one that you can limit the search just to a particular book?
Aaron Bertrand [SQL Server MVP] wrote:
>? There sure is.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> There's one that you can limit the search just to a particular book?
Well, I searched for "rename index SQL Server" at msdn.microsoft.com and
sp_rename came up on the first page (and it was the first one that said
anything about Transact-SQL).
If you want an easier way to limit you search to SQL Server only, then
install Books Online on your own computer (if it isn't there already).
See http://www.aspfaq.com/2229 for information about where to find Books
Online on your own computer, and where to get it if you don't already have
it installed. The download for SQL Server 2000 is currently broken, so you
may want to download the SQL Server 2005 edition. However, note that some
content is not applicable to SQL Server 2000 (though the basic gist of the
sp_rename topic should get you going), and you will need to have the .Net
2.0 Framework installed.
A
index at the time that the table is created, but how do you rename an
existing index.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Check out sp_rename in the Books Online.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d63becf3fe8@.uwe...
> What's a SQL statement for renaming an existing index. I know how to add
> an
> index at the time that the table is created, but how do you rename an
> existing index.
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> What's a SQL statement for renaming an existing index.
Books Online is your friend.
EXEC sp_rename 'tablename.indexname', 'newname', 'index'|||too bad there's not a search.
Aaron Bertrand [SQL Server MVP] wrote:
>Books Online is your friend.
>EXEC sp_rename 'tablename.indexname', 'newname', 'index'
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||To make sure that I don't rename something else, I'm hoping to verify that
the "table.indexname" is also not the name of a column. I want to get the
correct item to rename. Maybe that can't be done with SP_Rename. since it
takes a string, not an object_ID.
HockeyFan wrote:
>too bad there's not a search.
>
>
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> too bad there's not a search.
But there is. Also, I found the topic very quickly in the index by typing
'renaming indexes'.
Hope this helps.
Dan Guzman
SQL Server MVP
"HockeyFan via webservertalk.com" <u16651@.uwe> wrote in message
news:58d681f6f81ec@.uwe...
> too bad there's not a search.
> Aaron Bertrand [SQL Server MVP] wrote:
> --
> E. coli Happens.
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> To make sure that I don't rename something else, I'm hoping to verify that
> the "table.indexname" is also not the name of a column.
That's what the third parameter is for. Notice I say 'index' and not
'column'...|||> too bad there's not a search.
? There sure is.|||There's one that you can limit the search just to a particular book?
Aaron Bertrand [SQL Server MVP] wrote:
>? There sure is.
E. coli Happens.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||> There's one that you can limit the search just to a particular book?
Well, I searched for "rename index SQL Server" at msdn.microsoft.com and
sp_rename came up on the first page (and it was the first one that said
anything about Transact-SQL).
If you want an easier way to limit you search to SQL Server only, then
install Books Online on your own computer (if it isn't there already).
See http://www.aspfaq.com/2229 for information about where to find Books
Online on your own computer, and where to get it if you don't already have
it installed. The download for SQL Server 2000 is currently broken, so you
may want to download the SQL Server 2005 edition. However, note that some
content is not applicable to SQL Server 2000 (though the basic gist of the
sp_rename topic should get you going), and you will need to have the .Net
2.0 Framework installed.
A
Wednesday, March 21, 2012
INDEX ON VARIABLE table
I create a variable table using this statement in my UDF ,I'm like to put an
index on of its field (Internal_ID),How can I do it?
DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
NUMERIC(9),COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9))
ThanksYou cannot explicitly create indexes on a table variable, rather only those
created by unique or primary key constraints. If internal_id is unique,
create a unique constraint on it:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9) UNIQUE
)
If it isn't, make it the first column of a unique constraint, and include
additional columns that would make it unique.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> I create a variable table using this statement in my UDF ,I'm like to put
an
> index on of its field (Internal_ID),How can I do it?
> DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
> NUMERIC(9),COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9))
> Thanks
>|||Dose it make a join on internal_ID boosted.The reason I was trying to create
index on it is that I'm joing this varaible table with another table on
internal_id ..
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Actually ENROLLMENT_ID is unique so I can 100% garanty that combination of
ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
below?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Just make both part of the unique constraint or primary key:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
)
or
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Actually ENROLLMENT_ID is unique so I can 100% garanty that combination
of
> ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
> below?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Sure can.
Just keep in mind that the optimizer doesn't maintain statistics for table
variables like it does for regular and temp tables. So make sure you
benchmark solutions with temp tables as well, comparing the performence with
table variables.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Dose it make a join on internal_ID boosted.The reason I was trying to
create
> index on it is that I'm joing this varaible table with another table on
> internal_id ..
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uwkDwU1VFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Just make both part of the unique constraint or primary key:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
> )
> or
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
> )
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> of
> message
> include
> put
>|||I know that sql server hugely uses statistics to execute queries iin the
best way,but I just wondred how it uses statistcs and what are they? why we
should update them with fullscan sometimes?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Sure can.
> Just keep in mind that the optimizer doesn't maintain statistics for table
> variables like it does for regular and temp tables. So make sure you
> benchmark solutions with temp tables as well, comparing the performence
> with
> table variables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> create
> message
> include
> put
>|||J-R, try this whitepaper:
http://support.microsoft.com/defaul...kb;en-us;322096
BG, SQL Server MVP
www.SolidQualityLearning.com
"J-R" <RayAll@.microsft.com> wrote in message
news:OeCMv01VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> I know that sql server hugely uses statistics to execute queries iin the
> best way,but I just wondred how it uses statistcs and what are they? why
we
> should update them with fullscan sometimes?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
table
unique,
to
>
index on of its field (Internal_ID),How can I do it?
DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
NUMERIC(9),COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9))
ThanksYou cannot explicitly create indexes on a table variable, rather only those
created by unique or primary key constraints. If internal_id is unique,
create a unique constraint on it:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9) UNIQUE
)
If it isn't, make it the first column of a unique constraint, and include
additional columns that would make it unique.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> I create a variable table using this statement in my UDF ,I'm like to put
an
> index on of its field (Internal_ID),How can I do it?
> DECLARE @.TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID
> NUMERIC(9),COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9))
> Thanks
>|||Dose it make a join on internal_ID boosted.The reason I was trying to create
index on it is that I'm joing this varaible table with another table on
internal_id ..
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Actually ENROLLMENT_ID is unique so I can 100% garanty that combination of
ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
below?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
> You cannot explicitly create indexes on a table variable, rather only
> those
> created by unique or primary key constraints. If internal_id is unique,
> create a unique constraint on it:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9) UNIQUE
> )
> If it isn't, make it the first column of a unique constraint, and include
> additional columns that would make it unique.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:etd1LM0VFHA.2124@.TK2MSFTNGP14.phx.gbl...
> an
>|||Just make both part of the unique constraint or primary key:
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
)
or
DECLARE @.TBEnrollmentsForActiveClients TABLE
(
ENROLLMENT_ID NUMERIC(9),
COURSE_CODE VARCHAR(3),
COURSE_YEAR NUMERIC(5),
COURSE_SESSION NUMERIC(5),
INTERNAL_ID NUMERIC(9),
PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Actually ENROLLMENT_ID is unique so I can 100% garanty that combination
of
> ENROLLMENT_ID and INTERNAL_ID is also unique,how can I say this in code
> below?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Sure can.
Just keep in mind that the optimizer doesn't maintain statistics for table
variables like it does for regular and temp tables. So make sure you
benchmark solutions with temp tables as well, comparing the performence with
table variables.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ray5531" <RayAll@.microsft.com> wrote in message
news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Dose it make a join on internal_ID boosted.The reason I was trying to
create
> index on it is that I'm joing this varaible table with another table on
> internal_id ..
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:uCNjDU0VFHA.2492@.TK2MSFTNGP10.phx.gbl...
include
put
>|||Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:uwkDwU1VFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Just make both part of the unique constraint or primary key:
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> UNIQUE(ENROLLMENT_ID, INTERNAL_ID)
> )
> or
> DECLARE @.TBEnrollmentsForActiveClients TABLE
> (
> ENROLLMENT_ID NUMERIC(9),
> COURSE_CODE VARCHAR(3),
> COURSE_YEAR NUMERIC(5),
> COURSE_SESSION NUMERIC(5),
> INTERNAL_ID NUMERIC(9),
> PRIMARY KEY(ENROLLMENT_ID, INTERNAL_ID)
> )
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#BGDjd0VFHA.2700@.TK2MSFTNGP12.phx.gbl...
> of
> message
> include
> put
>|||I know that sql server hugely uses statistics to execute queries iin the
best way,but I just wondred how it uses statistcs and what are they? why we
should update them with fullscan sometimes?
Thanks
"Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in message
news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Sure can.
> Just keep in mind that the optimizer doesn't maintain statistics for table
> variables like it does for regular and temp tables. So make sure you
> benchmark solutions with temp tables as well, comparing the performence
> with
> table variables.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray5531" <RayAll@.microsft.com> wrote in message
> news:#hu8xY0VFHA.1044@.TK2MSFTNGP10.phx.gbl...
> create
> message
> include
> put
>|||J-R, try this whitepaper:
http://support.microsoft.com/defaul...kb;en-us;322096
BG, SQL Server MVP
www.SolidQualityLearning.com
"J-R" <RayAll@.microsft.com> wrote in message
news:OeCMv01VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> I know that sql server hugely uses statistics to execute queries iin the
> best way,but I just wondred how it uses statistcs and what are they? why
we
> should update them with fullscan sometimes?
> Thanks
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.solidqualitylearning.com> wrote in
message
> news:eeEcYV1VFHA.2572@.TK2MSFTNGP14.phx.gbl...
table
unique,
to
>
Index on temp table?
Hi,
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, SimonHi Simon,
You can create index on tem table. To view the information about index
please look into tempdb.
Regards,
Jagan Mohan
MCDBA
"SBeetham" wrote:
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||Ok, Cool... I can see the tempdb info... Thanks.
"Jagan Mohan" wrote:
> Hi Simon,
> You can create index on tem table. To view the information about index
> please look into tempdb.
> Regards,
> Jagan Mohan
> MCDBA
> "SBeetham" wrote:
>
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, SimonHi Simon,
You can create index on tem table. To view the information about index
please look into tempdb.
Regards,
Jagan Mohan
MCDBA
"SBeetham" wrote:
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||Ok, Cool... I can see the tempdb info... Thanks.
"Jagan Mohan" wrote:
> Hi Simon,
> You can create index on tem table. To view the information about index
> please look into tempdb.
> Regards,
> Jagan Mohan
> MCDBA
> "SBeetham" wrote:
>
Monday, March 19, 2012
Index on a Temp Table?
Hi,
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, Simonit's the same syntax as creating an index on a regular table.|||yes on # (local) and ## (global) prefixed tables. They are just tables and
can have everything that normal table have.
@. (local variable) tables can only have constraints defined in the DDL.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
> an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||SBeetham wrote:
> Hi,
> Had a quick look around BOL and can't seem to find whether one can
> create an index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
You can create one.
Create Table #test (col1 int)
Create unique Clustered Index test_idx on #test(col1)
David Gugick
Imceda Software
www.imceda.com|||Even though it might not find its place on BOL, it is quite possible for
temp and global temp tables.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||From the Books Online article "CREATE INDEX":
"Indexes can be created on a temporary table."
Steve Kass
Drew University
SBeetham wrote:
>Hi,
>Had a quick look around BOL and can't seem to find whether one can create a
n
>index on a temp table?
>Can anyone shed any light on this, please?
>Cheers, Simon
>
>
>
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, Simonit's the same syntax as creating an index on a regular table.|||yes on # (local) and ## (global) prefixed tables. They are just tables and
can have everything that normal table have.
@. (local variable) tables can only have constraints defined in the DDL.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
> an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||SBeetham wrote:
> Hi,
> Had a quick look around BOL and can't seem to find whether one can
> create an index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
You can create one.
Create Table #test (col1 int)
Create unique Clustered Index test_idx on #test(col1)
David Gugick
Imceda Software
www.imceda.com|||Even though it might not find its place on BOL, it is quite possible for
temp and global temp tables.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||From the Books Online article "CREATE INDEX":
"Indexes can be created on a temporary table."
Steve Kass
Drew University
SBeetham wrote:
>Hi,
>Had a quick look around BOL and can't seem to find whether one can create a
n
>index on a temp table?
>Can anyone shed any light on this, please?
>Cheers, Simon
>
>
>
Friday, February 24, 2012
Index Creation = 100% CPU Utilization
I have a large table in a production environment that I needs to have an
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavance
Set the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance
index created on it. Its already got a clustered index on it, and I need to
create a non-clustered index. On creation, the CPU usage hits 100%, which
brings response time of the application over reasonable limits. Is there any
way to limit the CPU Usage for this task, or is there any other workaround to
create this index without bringing the application offline?
SQL Server 2000 SP3
P4 - 4 x 1GB
4GB Memory
Thanks in adavance
Set the DOP at the server level to 2 processors. That way there will be two
for the index creation and 2 for everyone else. It will take longer for the
index to be created but it won't affect the other users as much.
Andrew J. Kelly SQL MVP
"g$" <g@.discussions.microsoft.com> wrote in message
news:EF7C237E-1135-4E9D-AB10-450B2C031815@.microsoft.com...
> I have a large table in a production environment that I needs to have an
> index created on it. Its already got a clustered index on it, and I need
to
> create a non-clustered index. On creation, the CPU usage hits 100%, which
> brings response time of the application over reasonable limits. Is there
any
> way to limit the CPU Usage for this task, or is there any other workaround
to
> create this index without bringing the application offline?
> SQL Server 2000 SP3
> P4 - 4 x 1GB
> 4GB Memory
> Thanks in adavance
Subscribe to:
Posts (Atom)