Hello SQLServer-specialists!
I'm running a database which contains on table, where the customer is
allowed to import data into.
This table also has a column, which the customer should set to his name, so
that you know who imported the record.
The problem is, that the customer is not restricted in setting this columns
value, so he also could set it to 'foo'!
Now I want to ensure, that the value is always set to the logged on user,
when importing data.
I know, that using a view and an INSTEAD OF trigger would do the job.
But as the table has abount 250 columns, I try to find an easier way.
Is it possible to write a trigger, which executes just before the insert,
and is able to replace a columns value?
Thanks in advance!
MaxHi there,
why not setting up a UPDATE / INSERT trigger then modifying the users
name, like this one below which takes the SUSER_SNAME as a input, make
sure you modify that, to eventually reflect your enviroment:
CREATE TRIGGER SomeTrigger ON SOmeTable
FOR INSERT
AS
BEGIN
UPDATE SomeTable SET ModifiedColumn = SUSER_SNAME
FROM SomeTable S
INNER JOIN INSERTED I
ON S.PKColumn = I.PKColumn
END
HTH, Jens Suessmeyer,
http://www.sqlserver2005.de
--|||Markus,
You can use the system function SYSTEM_USER to get the current username.
You can create an AFTER Trigger for inserts and update the inserted rows
Username with the SYSTEM_USER function.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>|||Hello!
Thanks for your answers!
I now tried the following
CREATE TRIGGER ModificationTrigger ON TheTableToInsertTo
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO TheTableToInsertTo
SELECT col1, col2, suser_sname()
FROM inserted
END
First I thought, that inserting into the same table, as the trigger should
instead of an insert, could make problems, but it works perfectly!!!
Greetings,
Max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:OqyIvYMTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> Hello SQLServer-specialists!
> I'm running a database which contains on table, where the customer is
> allowed to import data into.
> This table also has a column, which the customer should set to his name,
> so that you know who imported the record.
> The problem is, that the customer is not restricted in setting this
> columns value, so he also could set it to 'foo'!
> Now I want to ensure, that the value is always set to the logged on user,
> when importing data.
> I know, that using a view and an INSTEAD OF trigger would do the job.
> But as the table has abount 250 columns, I try to find an easier way.
> Is it possible to write a trigger, which executes just before the insert,
> and is able to replace a columns value?
> Thanks in advance!
> Max
>
Showing posts with label customer. Show all posts
Showing posts with label customer. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Replacement CD
Hi,
I had to reinstall the PC due to an earlier crash - Apparently my customer
lost the original SQL Server Personal Edition CDROM - I do have the license
Key and other details - Is there a way I can order replacement CDROMs from
Microsoft for this product that I purchased and if so, how/where to call?
Anyone had a similar experience and can share the wisdom?
Thanks very much,
Raj> I had to reinstall the PC due to an earlier crash - Apparently my customer
> lost the original SQL Server Personal Edition CDROM - I do have the
license
> Key and other details - Is there a way I can order replacement CDROMs from
> Microsoft for this product that I purchased and if so, how/where to call?
> Anyone had a similar experience and can share the wisdom?
Check if this is helpful:
http://support.microsoft.com/default.aspx?scid=kb;en-us;171931.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
I had to reinstall the PC due to an earlier crash - Apparently my customer
lost the original SQL Server Personal Edition CDROM - I do have the license
Key and other details - Is there a way I can order replacement CDROMs from
Microsoft for this product that I purchased and if so, how/where to call?
Anyone had a similar experience and can share the wisdom?
Thanks very much,
Raj> I had to reinstall the PC due to an earlier crash - Apparently my customer
> lost the original SQL Server Personal Edition CDROM - I do have the
license
> Key and other details - Is there a way I can order replacement CDROMs from
> Microsoft for this product that I purchased and if so, how/where to call?
> Anyone had a similar experience and can share the wisdom?
Check if this is helpful:
http://support.microsoft.com/default.aspx?scid=kb;en-us;171931.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
Wednesday, March 7, 2012
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database Architect
Sounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>
|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM
|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect
|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>
|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM
|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database Architect
Sounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>
|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM
|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect
|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>
|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM
|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>
Subscribe to:
Posts (Atom)