Hi,
I have a table which contains a Create datetimefield which has a default on the current date , a priorityfield and another datetimefield which will be the due date and has to be calculated by the first date and the priority field,
How can i do this and what fields must i have.
Does someone does this?
can someone help me with this?
Kind regards Wimwhat whould happen to the due-date if the priority changed?|||what whould happen to the due-date if the priority changed?
The due date has to change to!|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint|||use a computed column for the due date, fe:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER
, DUEDATE AS DATEADD(DD, PRIO, GETDATE())
)
GO
INSERT INTO TAB1 (PRIO) VALUES (1)
GO
SELECT * FROM TAB1
UPDATE TAB1 SET PRIO = 10
SELECT * FROM TAB1
GO
DROP TABLE TAB1
GO
EDIT: Just to be sure; either have a default prio or have a not null constraint
And what if i use the priority through an foreign key an have several prioritys which uses different time like:
prio 1 = 1day
prio 2 =1 week
prio3 = 1 month
How will it look like then?|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO|||well, it could look like:
CREATE TABLE TAB1 (
FIRSTDATE DATETIME DEFAULT GETDATE()
, PRIO INTEGER NOT NULL
, DUEDATE AS CASE PRIO
WHEN 1 THEN DATEADD(DD, 1, FIRSTDATE)
WHEN 2 THEN DATEADD(WW, 1, FIRSTDATE)
WHEN 3 THEN DATEADD(MM, 1, FIRSTDATE)
ELSE DATEADD(YY, 1, FIRSTDATE)
END
)
GO
Thanx alot man, that hit the spot!!
I really appreciated your help..
Cheers Wim
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Tuesday, March 27, 2012
Auto Create trigger after re-initialization completed
Hi all,
Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!
Hi, Stephanie,
If I understand your question correctly, you wan to create a trigger on subscriber db right after replication agent created replicating table on subscriber database. If so, you can use post snapshot script.
To specify a post snapshot script in Management Studio, right click a publication under replication node, choose Properties context menu to bring up Publication Properties dialog, on the left pane treeview, click snapshot, you should see the input field to specify Pre snapshot script and post snapshot script under "Run Additional scripts" section.
Hope that helps,
Zhiqiang
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thx for reply
I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
The replication from oracle to MSSQL2005 and the snapshot format is set to 'character' automatically. Is it possible to create trigger in such case? Thanks in advance
|||Stephanie,
You're right, for snapshot using Character mode, the "run additional scripts" option is not available under Publication Properties dialog (disabled in the UI).
In this case, you can add an extra step to create your subscriber table trigger after the distribution or merge agent step in your synchronization job. Following are the steps:
1. Find your sync job under Management Studio --> Object Explorer --> SQL Server Agent --> Jobs --> Your distribution/merge agent job name
2. Right click the sync job, pick Properties from context menu, this will bring up Job Properties dialog
3. On the left side pane under "Select a page", click Steps
4. Insert a new step right after step "Run Agent" to execute your script to create the subscriber table trigger,
Example:
step name: Run Post snapshot script to create subscriber table trigger
type: Operating System (cmdexec)
RunAs: SQL Server Agent Service Account
Command: sqlcmd.exe -E -Ssubscriberinstancename -dsubscriberdbname -Q"create trigger postsnapshottrigger on [subscriberdbname].[dbo].[subscribertable] for update,insert,delete AS select * from sys.databases"
5. Change the Advanced Option for the new step:
On Success action: Quit the job reporting success
On failure action: Go to the next step
6. Change the Advanced Option for step "Run Agent":
On success action: Go to the next step
Leave on failure action unchanged
The original setting for "On success action" is "quite the job reporting success", this will result in the new step you just added being ignored thus we need this change.
7. Reinitialize your subscription and rerun your sync job
I have tested this scenario on a character mode merge publication and it worked just fine (I'm not using Oracle in this test).
If you're not using SQL Server agent to run your jobs, for example, using a batch file to run sync command directly, you can just add the command to create trigger after sync command.
Let me know if this helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||The table is re-created after re-initialize but the trigger didn't be created. I found that the agent job is keeping 'running'...
Just want to confirm that the distribution job with the 1step 'Distribution agent startup message' , right?
Thanks in advance
|||
Hi, stephanie,
I just realized there is one issue with my approach if your distribution agent is set to run continuously (there is a -continuous prameter for the distribution command for step Run Agent), this way your new step will never get a chance to run since the Run Agent step is running continuously.
If your oracle publication can be re-created, you can walk through the New Oracle Publication Wizard and at the page of "Wizard Actions", uncheck "Create the publication" option and check "Generate a script file with steps to create the publication", once the script is created, modify the @.post_snapshot_script pointing to the location of your script to create the trigger, then run this generated script to create your oracle publication.
If you don't want to re-create your oracle publication, you can modify the generated snapshot files to create the trigger after the subscriber table is created (there should be a .sch file for your replicating table), then re-initialize your publication. Note this manual step need to be done each time you re-generated the snapshot files
You can also create a new SQL Server agent job to just create the trigger and have it to run continuously. The logic of your script should be: if subscriber table exist and if the trigger doesn't exist, then create the trigger. This will have some system overhead and your trigger may miss some transactions if updates to the table occurred and before the trigger is created.
Let me know if it helps.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||
I prefer option 1 but i found that there is no '@.post_snapshot_script ' in the scripted generated... should I append this option into the script? Also, there are 3 parts in the script, running
sys.sp_addlogreader_agent, exec sp_addpublication,
exec sp_addpublication_snapshot and sp_addarticle
where should I append it? Thanks in advance.
|||
@.post_snapshot_script is a parameter of sp_addpublication so it should be appended to the sp_addpublication call, see Books Online help topic for sp_addpublication for details of this parameter.
Let me know if you encounter any issues.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks a lot, it perfectly solve our problem!
|||That's great!
By the way, I've submitted a feature request to have new publication wizard support the addition of pre/post snapshot script, this issue may be addressed in a future release of SQL Server so you configure pre/post snapshot script without needing the manual step of modifying and running the replication script as a workaround.
Zhiqiang Feng
This posting is provided "AS IS" with no warranties, and confers no rights.
Auto Create trigger after re-initialization completed
Is it possible to create a trigger after creation of table during
reinitialization? if so, how can I do that? Thanks in advance!
Do it through a post snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
> Is it possible to create a trigger after creation of table during
> reinitialization? if so, how can I do that? Thanks in advance!
>
|||What about if the replication from oracle to MSSQL2005 and the snapshot
format is set to 'character'? Thanks in advance.
"Hilary Cotter" wrote:
> Do it through a post snapshot command.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
>
>
|||A character format for the snapshot refers to the data.
A post snapshot command is a script file that you specify. Once the
snapshot completes, SQL Server will open the script file that you specify
and execute anything in the script.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...[vbcol=seagreen]
> What about if the replication from oracle to MSSQL2005 and the snapshot
> format is set to 'character'? Thanks in advance.
> "Hilary Cotter" wrote:
|||Thx for reply. I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
Please advise. Thanks
"Michael Hotek" wrote:
> A character format for the snapshot refers to the data.
> A post snapshot command is a script file that you specify. Once the
> snapshot completes, SQL Server will open the script file that you specify
> and execute anything in the script.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...
>
>
|||Forgot about that restriction. There was a reason for that restriction, but
I can't remember what it was right now.
One workaround is to create a DDL trigger like the following:
create trigger test
on database
for create_table
as
declare @.parentid int
select @.parentid = object_id from sys.tables where name = 'MyTable'
print @.parentid
if @.parentid is null
return
if exists (select 1 from sys.triggers where parent_id = @.parentid
and name = 'MyTestTrigger')
return
exec sp_executesql N'create trigger MyTestTrigger on MyTable for insert as
raiserror(''Test'',16,10)'
go
create table MyTable
(id int)
go
I can't think of any other workarounds at the moment.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2343A716-0B57-4972-A9B2-C3B4FECA72D9@.microsoft.com...[vbcol=seagreen]
> Thx for reply. I can't find the option 'After applying the snapshot,
> execute
> this script text box' in Snapshot page of the Publication Properties as
> stated in Online Books. Also, I found a note in online book stating
> 'These
> options are not available if the Snapshot format option is set to
> Character.'
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
> Please advise. Thanks
> "Michael Hotek" wrote:
reinitialization? if so, how can I do that? Thanks in advance!
Do it through a post snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
> Is it possible to create a trigger after creation of table during
> reinitialization? if so, how can I do that? Thanks in advance!
>
|||What about if the replication from oracle to MSSQL2005 and the snapshot
format is set to 'character'? Thanks in advance.
"Hilary Cotter" wrote:
> Do it through a post snapshot command.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:C8C2E193-7228-4A86-8E30-A6523CF62EFA@.microsoft.com...
>
>
|||A character format for the snapshot refers to the data.
A post snapshot command is a script file that you specify. Once the
snapshot completes, SQL Server will open the script file that you specify
and execute anything in the script.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...[vbcol=seagreen]
> What about if the replication from oracle to MSSQL2005 and the snapshot
> format is set to 'character'? Thanks in advance.
> "Hilary Cotter" wrote:
|||Thx for reply. I can't find the option 'After applying the snapshot, execute
this script text box' in Snapshot page of the Publication Properties as
stated in Online Books. Also, I found a note in online book stating 'These
options are not available if the Snapshot format option is set to Character.'
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
Please advise. Thanks
"Michael Hotek" wrote:
> A character format for the snapshot refers to the data.
> A post snapshot command is a script file that you specify. Once the
> snapshot completes, SQL Server will open the script file that you specify
> and execute anything in the script.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:A5E40A16-E2EF-4339-9F4E-D005820DEE45@.microsoft.com...
>
>
|||Forgot about that restriction. There was a reason for that restriction, but
I can't remember what it was right now.
One workaround is to create a DDL trigger like the following:
create trigger test
on database
for create_table
as
declare @.parentid int
select @.parentid = object_id from sys.tables where name = 'MyTable'
print @.parentid
if @.parentid is null
return
if exists (select 1 from sys.triggers where parent_id = @.parentid
and name = 'MyTestTrigger')
return
exec sp_executesql N'create trigger MyTestTrigger on MyTable for insert as
raiserror(''Test'',16,10)'
go
create table MyTable
(id int)
go
I can't think of any other workarounds at the moment.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2343A716-0B57-4972-A9B2-C3B4FECA72D9@.microsoft.com...[vbcol=seagreen]
> Thx for reply. I can't find the option 'After applying the snapshot,
> execute
> this script text box' in Snapshot page of the Publication Properties as
> stated in Online Books. Also, I found a note in online book stating
> 'These
> options are not available if the Snapshot format option is set to
> Character.'
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/b7bb1e4c-5b48-4bb1-9dc8-47c911f2cc82.htm
> Please advise. Thanks
> "Michael Hotek" wrote:
Auto create stats on but DTA recommends creating them
So I am running a query against a database that has Auto Create statistics
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?
Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not create
> those statistics that DTA was recommending ?
>
|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
sql
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?
Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not create
> those statistics that DTA was recommending ?
>
|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
sql
Auto create stats on but DTA recommends creating them
So I am running a query against a database that has Auto Create statistics
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not create
> those statistics that DTA was recommending ?
>|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
>> So I am running a query against a database that has Auto Create
>> statistics
>> turned on and then what I did was pass this same query to DTA and it
>> recommends creating statistics as an improvement. Not to mention, it also
>> said to create some indices.
>> So the question I have is, when I did run the query, why does SQL not
>> create
>> those statistics that DTA was recommending ?
>>|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not create
> those statistics that DTA was recommending ?
>|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
>> So I am running a query against a database that has Auto Create
>> statistics
>> turned on and then what I did was pass this same query to DTA and it
>> recommends creating statistics as an improvement. Not to mention, it also
>> said to create some indices.
>> So the question I have is, when I did run the query, why does SQL not
>> create
>> those statistics that DTA was recommending ?
>>|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
> > Hi Hassan
> >
> > Are these statistics on multiple columns?
> >
> > John
> >
> > "Hassan" wrote:
> >
> >> So I am running a query against a database that has Auto Create
> >> statistics
> >> turned on and then what I did was pass this same query to DTA and it
> >> recommends creating statistics as an improvement. Not to mention, it also
> >> said to create some indices.
> >>
> >> So the question I have is, when I did run the query, why does SQL not
> >> create
> >> those statistics that DTA was recommending ?
> >>
> >>
>
Auto create stats on but DTA recommends creating them
So I am running a query against a database that has Auto Create statistics
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not crea
te
> those statistics that DTA was recommending ?
>|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
>|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
turned on and then what I did was pass this same query to DTA and it
recommends creating statistics as an improvement. Not to mention, it also
said to create some indices.
So the question I have is, when I did run the query, why does SQL not create
those statistics that DTA was recommending ?Hi Hassan
Are these statistics on multiple columns?
John
"Hassan" wrote:
> So I am running a query against a database that has Auto Create statistics
> turned on and then what I did was pass this same query to DTA and it
> recommends creating statistics as an improvement. Not to mention, it also
> said to create some indices.
> So the question I have is, when I did run the query, why does SQL not crea
te
> those statistics that DTA was recommending ?
>|||I wish I had it saved .. Not sure..
Is that a bug where if it is on multiple columns, the auto create stats
doesnt automatically take care of it when i run the query ?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...[vbcol=seagreen]
> Hi Hassan
> Are these statistics on multiple columns?
> John
> "Hassan" wrote:
>|||No, it is not a bug. John may refer to the fact that statistics on multiple
columns (non-indexed) are not created automatically by SQL Server. You need
to create these statistics manually. But once these multi-columns statistics
are created, SQL Server will automatically update them if Auto Update
Statistics is set to True.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>|||Hi Hassan
Ben has said that the AUTO_CREATE_STATISTICS will only create statistics for
a single column and this is by design.
Unless you have explicitly deleted the DTA session, you should be able to
review the output again if you go back into DTA.
John
"Hassan" wrote:
> I wish I had it saved .. Not sure..
> Is that a bug where if it is on multiple columns, the auto create stats
> doesnt automatically take care of it when i run the query ?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:E1D0BBFB-4A56-4FA2-9134-609341226144@.microsoft.com...
>
Auto Create Stats & Auto Update stats
I've inherited a server where auto create stats and auto update stats have b
een turned off. There is a weekly job which executes Update Statistics on e
ach table with the norecompute clause. In looking at traces I'm seeing quit
e a few missing statistics
entries.
When is it appropriate to turn off auto create stats?Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
een turned off. There is a weekly job which executes Update Statistics on e
ach table with the norecompute clause. In looking at traces I'm seeing quit
e a few missing statistics
entries.
When is it appropriate to turn off auto create stats?Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
Auto Create Stats & Auto Update stats
I've inherited a server where auto create stats and auto update stats have been turned off. There is a weekly job which executes Update Statistics on each table with the norecompute clause. In looking at traces I'm seeing quite a few missing statistics
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
entries.
When is it appropriate to turn off auto create stats?
Peter,
as I understand it, if you've just created a table and are loading it with
loads of rows, you might disable the creation of statistics because the
performance overhead is more than you can afford. Likewise if the table
already exists with statistics and requires loading, you might run
sp_autostats or the UPDATE STATISTICS command with the WITH NORECOMPUTE
option. However, this is only a temporary measure and once loaded it is
normal to have it auto updating, or else incorrect query plans may result.
Regards,
Paul Ibison
Auto Create Stats
Is it a good idea to have Auto create stats and Auto
update stats ON in a database
Does it slow down performance while loading large amounts
of data
SanjayHi Sanjay
No, the updating of stats does not happen during a load. When you run a
query, and SQL Server notices the stats are out of date, then it will auto
update them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjay|||Sanjay,
Nothing is free, so Auto Create Stats does cost something from the server.
However, I understand that it tries to do that work during low periods and
stay out of the way of other work. And remember, statistics that are not in
sync with the data can cause the optimizer to guess wrong.
There is a discussion of the issues at:
http://www.sql-server-performance.com/statistics.asp
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjaysql
update stats ON in a database
Does it slow down performance while loading large amounts
of data
SanjayHi Sanjay
No, the updating of stats does not happen during a load. When you run a
query, and SQL Server notices the stats are out of date, then it will auto
update them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjay|||Sanjay,
Nothing is free, so Auto Create Stats does cost something from the server.
However, I understand that it tries to do that work during low periods and
stay out of the way of other work. And remember, statistics that are not in
sync with the data can cause the optimizer to guess wrong.
There is a discussion of the issues at:
http://www.sql-server-performance.com/statistics.asp
Russell Fields
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:000a01c39cb9$c906e160$a601280a@.phx.gbl...
> Is it a good idea to have Auto create stats and Auto
> update stats ON in a database
> Does it slow down performance while loading large amounts
> of data
> Sanjaysql
Auto Create Statistics
We have accidentally had the "Auto Create Statistics" turned set to true on
one of our production databases. When we told the group responsible for the
application for this database, they said they have been having performance
issues and are enquiring if having this setting turned on could be the cause
of that.
From BOL, with automatic statistics, the statistics are updated periodically
as the data in the table changes. A statistics update occurs whenever the
statistics in a query execution fail a test for the current statistics.
Statistical information is updated whenever approximately 20 percent of the
data rows have been changed.
Is it possible that this overhead could have an affect on database
performance?
Also, they asked if we could give then a report for displaying all the
statistics. I know the "DBBC SHOW_STATISTICS" command will display the
statistics if you supply the table and index parameters. The database is
quite large with many tables and indexes. I could probably write a script
using cursors but I am wondering if there is any other way for generating a
report for all of the statistics for the database.Hi Loren
This setting is ON by default on all databases, and it is usually a good
idea to leave it on. In addition, this setting is not the same as AUTO
UPDATE STATISTICS. Since this was most likely on since the time the db was
created, it's unlikely that it is the cause for any recent performance
problems.
AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
columns. AUTO UPDATE STATISTICS tells SQL Server to update index statistics
when they get stale.
Actually looking at statistics is rarely needed, and most people do it only
when troubleshooting one particular query that is misbehaving. Why do they
want a report for ALL the statistics when they are subject to frequent
change? By the time they got through looking at the whole report, the values
would probably have changed.
I would think the DBCC SHOW_STATISTICS would be good enough, when they need
to look at particular statistics for a particular table or index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
> We have accidentally had the "Auto Create Statistics" turned set to true
> on one of our production databases. When we told the group responsible for
> the application for this database, they said they have been having
> performance issues and are enquiring if having this setting turned on
> could be the cause of that.
> From BOL, with automatic statistics, the statistics are updated
> periodically as the data in the table changes. A statistics update occurs
> whenever the statistics in a query execution fail a test for the current
> statistics. Statistical information is updated whenever approximately 20
> percent of the data rows have been changed.
> Is it possible that this overhead could have an affect on database
> performance?
> Also, they asked if we could give then a report for displaying all the
> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
> statistics if you supply the table and index parameters. The database is
> quite large with many tables and indexes. I could probably write a script
> using cursors but I am wondering if there is any other way for generating
> a report for all of the statistics for the database.
>|||As far as I know, this post was asking about Auto CREATE Statistics, not
AUTO UPDATE.
Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
recompiling has far less of a cost than the costs of using a bad plan
because your statistics are out of date. SQL 2005 also introduced AUTO
UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
but it will not impact the query that triggered the update, i.e. that query
will not have to wait for the update and the subsequent recompile.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
> Hello Kalen!
>
> As far as I know Auto Update Statistics cause changing Query Plans and
> causes SPs to be recompiled so this makes a performance problem. I know
> that if statistics would be ouf of date, then it's another problem. This
> seems kind of dilemma.
> I would be happy to hear your comments about this.
>
> --
> Ekrem Önsoy
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>|||Hello Kalen!
As far as I know Auto Update Statistics cause changing Query Plans and
causes SPs to be recompiled so this makes a performance problem. I know that
if statistics would be ouf of date, then it's another problem. This seems
kind of dilemma.
I would be happy to hear your comments about this.
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi Loren
> This setting is ON by default on all databases, and it is usually a good
> idea to leave it on. In addition, this setting is not the same as AUTO
> UPDATE STATISTICS. Since this was most likely on since the time the db was
> created, it's unlikely that it is the cause for any recent performance
> problems.
> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
> statistics when they get stale.
> Actually looking at statistics is rarely needed, and most people do it
> only when troubleshooting one particular query that is misbehaving. Why do
> they want a report for ALL the statistics when they are subject to
> frequent change? By the time they got through looking at the whole report,
> the values would probably have changed.
> I would think the DBCC SHOW_STATISTICS would be good enough, when they
> need to look at particular statistics for a particular table or index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update occurs
>> whenever the statistics in a query execution fail a test for the current
>> statistics. Statistical information is updated whenever approximately 20
>> percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a script
>> using cursors but I am wondering if there is any other way for generating
>> a report for all of the statistics for the database.
>|||Yes, I know that post was about CREATE STATISTICS. However, when you
mentioned Update Statistics, I wanted to have your comments about this.
Thanks for sharing your thoughts about this.
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O1gT8wSBIHA.4656@.TK2MSFTNGP04.phx.gbl...
> As far as I know, this post was asking about Auto CREATE Statistics, not
> AUTO UPDATE.
> Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
> recompiling has far less of a cost than the costs of using a bad plan
> because your statistics are out of date. SQL 2005 also introduced AUTO
> UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
> but it will not impact the query that triggered the update, i.e. that
> query will not have to wait for the update and the subsequent recompile.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
>> Hello Kalen!
>>
>> As far as I know Auto Update Statistics cause changing Query Plans and
>> causes SPs to be recompiled so this makes a performance problem. I know
>> that if statistics would be ouf of date, then it's another problem. This
>> seems kind of dilemma.
>> I would be happy to hear your comments about this.
>>
>> --
>> Ekrem Önsoy
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to
>> true on one of our production databases. When we told the group
>> responsible for the application for this database, they said they have
>> been having performance issues and are enquiring if having this setting
>> turned on could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database
>> is quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>>
>
one of our production databases. When we told the group responsible for the
application for this database, they said they have been having performance
issues and are enquiring if having this setting turned on could be the cause
of that.
From BOL, with automatic statistics, the statistics are updated periodically
as the data in the table changes. A statistics update occurs whenever the
statistics in a query execution fail a test for the current statistics.
Statistical information is updated whenever approximately 20 percent of the
data rows have been changed.
Is it possible that this overhead could have an affect on database
performance?
Also, they asked if we could give then a report for displaying all the
statistics. I know the "DBBC SHOW_STATISTICS" command will display the
statistics if you supply the table and index parameters. The database is
quite large with many tables and indexes. I could probably write a script
using cursors but I am wondering if there is any other way for generating a
report for all of the statistics for the database.Hi Loren
This setting is ON by default on all databases, and it is usually a good
idea to leave it on. In addition, this setting is not the same as AUTO
UPDATE STATISTICS. Since this was most likely on since the time the db was
created, it's unlikely that it is the cause for any recent performance
problems.
AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
columns. AUTO UPDATE STATISTICS tells SQL Server to update index statistics
when they get stale.
Actually looking at statistics is rarely needed, and most people do it only
when troubleshooting one particular query that is misbehaving. Why do they
want a report for ALL the statistics when they are subject to frequent
change? By the time they got through looking at the whole report, the values
would probably have changed.
I would think the DBCC SHOW_STATISTICS would be good enough, when they need
to look at particular statistics for a particular table or index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
> We have accidentally had the "Auto Create Statistics" turned set to true
> on one of our production databases. When we told the group responsible for
> the application for this database, they said they have been having
> performance issues and are enquiring if having this setting turned on
> could be the cause of that.
> From BOL, with automatic statistics, the statistics are updated
> periodically as the data in the table changes. A statistics update occurs
> whenever the statistics in a query execution fail a test for the current
> statistics. Statistical information is updated whenever approximately 20
> percent of the data rows have been changed.
> Is it possible that this overhead could have an affect on database
> performance?
> Also, they asked if we could give then a report for displaying all the
> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
> statistics if you supply the table and index parameters. The database is
> quite large with many tables and indexes. I could probably write a script
> using cursors but I am wondering if there is any other way for generating
> a report for all of the statistics for the database.
>|||As far as I know, this post was asking about Auto CREATE Statistics, not
AUTO UPDATE.
Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
recompiling has far less of a cost than the costs of using a bad plan
because your statistics are out of date. SQL 2005 also introduced AUTO
UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
but it will not impact the query that triggered the update, i.e. that query
will not have to wait for the update and the subsequent recompile.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
> Hello Kalen!
>
> As far as I know Auto Update Statistics cause changing Query Plans and
> causes SPs to be recompiled so this makes a performance problem. I know
> that if statistics would be ouf of date, then it's another problem. This
> seems kind of dilemma.
> I would be happy to hear your comments about this.
>
> --
> Ekrem Önsoy
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>|||Hello Kalen!
As far as I know Auto Update Statistics cause changing Query Plans and
causes SPs to be recompiled so this makes a performance problem. I know that
if statistics would be ouf of date, then it's another problem. This seems
kind of dilemma.
I would be happy to hear your comments about this.
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi Loren
> This setting is ON by default on all databases, and it is usually a good
> idea to leave it on. In addition, this setting is not the same as AUTO
> UPDATE STATISTICS. Since this was most likely on since the time the db was
> created, it's unlikely that it is the cause for any recent performance
> problems.
> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
> statistics when they get stale.
> Actually looking at statistics is rarely needed, and most people do it
> only when troubleshooting one particular query that is misbehaving. Why do
> they want a report for ALL the statistics when they are subject to
> frequent change? By the time they got through looking at the whole report,
> the values would probably have changed.
> I would think the DBCC SHOW_STATISTICS would be good enough, when they
> need to look at particular statistics for a particular table or index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to true
>> on one of our production databases. When we told the group responsible
>> for the application for this database, they said they have been having
>> performance issues and are enquiring if having this setting turned on
>> could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update occurs
>> whenever the statistics in a query execution fail a test for the current
>> statistics. Statistical information is updated whenever approximately 20
>> percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database is
>> quite large with many tables and indexes. I could probably write a script
>> using cursors but I am wondering if there is any other way for generating
>> a report for all of the statistics for the database.
>|||Yes, I know that post was about CREATE STATISTICS. However, when you
mentioned Update Statistics, I wanted to have your comments about this.
Thanks for sharing your thoughts about this.
--
Ekrem Önsoy
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O1gT8wSBIHA.4656@.TK2MSFTNGP04.phx.gbl...
> As far as I know, this post was asking about Auto CREATE Statistics, not
> AUTO UPDATE.
> Yes, AUTO UPDATE does have a cost, but in most cases, updating stats and
> recompiling has far less of a cost than the costs of using a bad plan
> because your statistics are out of date. SQL 2005 also introduced AUTO
> UPDATE STATISTICS ASYNC, so that the stats will be automatically updated,
> but it will not impact the query that triggered the update, i.e. that
> query will not have to wait for the update and the subsequent recompile.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4AF1D2DE-60B6-4C24-9F60-3BA208A222F1@.microsoft.com...
>> Hello Kalen!
>>
>> As far as I know Auto Update Statistics cause changing Query Plans and
>> causes SPs to be recompiled so this makes a performance problem. I know
>> that if statistics would be ouf of date, then it's another problem. This
>> seems kind of dilemma.
>> I would be happy to hear your comments about this.
>>
>> --
>> Ekrem Önsoy
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ekdDiSSBIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi Loren
>> This setting is ON by default on all databases, and it is usually a good
>> idea to leave it on. In addition, this setting is not the same as AUTO
>> UPDATE STATISTICS. Since this was most likely on since the time the db
>> was created, it's unlikely that it is the cause for any recent
>> performance problems.
>> AUTO CREATE STATISTICS tells SQL Server to create stats on unindexed
>> columns. AUTO UPDATE STATISTICS tells SQL Server to update index
>> statistics when they get stale.
>> Actually looking at statistics is rarely needed, and most people do it
>> only when troubleshooting one particular query that is misbehaving. Why
>> do they want a report for ALL the statistics when they are subject to
>> frequent change? By the time they got through looking at the whole
>> report, the values would probably have changed.
>> I would think the DBCC SHOW_STATISTICS would be good enough, when they
>> need to look at particular statistics for a particular table or index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Loren Z" <anonymous@.discussions.microsoft.com> wrote in message
>> news:e4gI9NSBIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> We have accidentally had the "Auto Create Statistics" turned set to
>> true on one of our production databases. When we told the group
>> responsible for the application for this database, they said they have
>> been having performance issues and are enquiring if having this setting
>> turned on could be the cause of that.
>> From BOL, with automatic statistics, the statistics are updated
>> periodically as the data in the table changes. A statistics update
>> occurs whenever the statistics in a query execution fail a test for the
>> current statistics. Statistical information is updated whenever
>> approximately 20 percent of the data rows have been changed.
>> Is it possible that this overhead could have an affect on database
>> performance?
>> Also, they asked if we could give then a report for displaying all the
>> statistics. I know the "DBBC SHOW_STATISTICS" command will display the
>> statistics if you supply the table and index parameters. The database
>> is quite large with many tables and indexes. I could probably write a
>> script using cursors but I am wondering if there is any other way for
>> generating a report for all of the statistics for the database.
>>
>>
>
Auto Create Statistics
Is it advisable to enable the auto create statistics for a database ? Why
may one not do so ? Using SQL 2K
Thanks"FR" <floydrev@.hotmail.com> wrote in message
news:O5ZGTGRSDHA.2152@.TK2MSFTNGP12.phx.gbl...
> Is it advisable to enable the auto create statistics for a database ? Why
> may one not do so ? Using SQL 2K
For OLAP systems, generally the answer is YES, but be aware that auto
create/update stats don't do fullscans on tables over 8Mb which can cause
bad optimizer decisions in larger tables and/or exceptionally skewed data.
For OLTP systems, it depends, partly for the reasons above for OLAP. But the
problems caused on an OLTP system can be far more significant, primarily
resulting in unexpected serialization due to a bad query plan caused by
non-representative statistics (becuase of not running a fullscan).
Auto update stats can also make debugging very difficult because the system
chooses to go ahead in a non-deterministic way (ie, how and when the user
operates the application) and regenerate stats. Personally, I prefer to have
auto create/update switched off on the large OLTP systems I deal with.
Experience has shown me that blindly going ahead and enabling auto update
stats can have some rather detrimental effects which take a long time to
diagnose and resolve because they are so subtle in their effects.
In addition there is a small but potentially significant overhead for the
auto update which may lead to serialization in some situation.
Fundamentally the problem of statistics which have not undergone a fullscan
is that they're not representative of the underlying data.
As an aside, statistics have two other problems.
Firstly, the optimizer assumes that data distributions between statistics
are mutually exclusive, although in reality this is sometimes not the case
at all.
Secondly, histogram statistics are only held for the first column of
composite indices, which can be a real pain. It's another consideration to
bear in mind when you choose columns in an index. For example, if you often
reference a table with certain columns as SArgable values (eg, Where
ActiveStatus=1 And PrintedStatus=0), you should consider whether there is
any benefit having both statuses in the same composite index. Only knowing a
likely distribution of your data will allow you to make an informed
decision. Sadly a lot of IT managers love to say "it could be any number of
rows" which doesn't help! It's one of those times when the DBA should be
speaking directly with the business.
For small databases you probably won't notice any problems when the auto
create/update is switched on.
Kind Regards, Howard
may one not do so ? Using SQL 2K
Thanks"FR" <floydrev@.hotmail.com> wrote in message
news:O5ZGTGRSDHA.2152@.TK2MSFTNGP12.phx.gbl...
> Is it advisable to enable the auto create statistics for a database ? Why
> may one not do so ? Using SQL 2K
For OLAP systems, generally the answer is YES, but be aware that auto
create/update stats don't do fullscans on tables over 8Mb which can cause
bad optimizer decisions in larger tables and/or exceptionally skewed data.
For OLTP systems, it depends, partly for the reasons above for OLAP. But the
problems caused on an OLTP system can be far more significant, primarily
resulting in unexpected serialization due to a bad query plan caused by
non-representative statistics (becuase of not running a fullscan).
Auto update stats can also make debugging very difficult because the system
chooses to go ahead in a non-deterministic way (ie, how and when the user
operates the application) and regenerate stats. Personally, I prefer to have
auto create/update switched off on the large OLTP systems I deal with.
Experience has shown me that blindly going ahead and enabling auto update
stats can have some rather detrimental effects which take a long time to
diagnose and resolve because they are so subtle in their effects.
In addition there is a small but potentially significant overhead for the
auto update which may lead to serialization in some situation.
Fundamentally the problem of statistics which have not undergone a fullscan
is that they're not representative of the underlying data.
As an aside, statistics have two other problems.
Firstly, the optimizer assumes that data distributions between statistics
are mutually exclusive, although in reality this is sometimes not the case
at all.
Secondly, histogram statistics are only held for the first column of
composite indices, which can be a real pain. It's another consideration to
bear in mind when you choose columns in an index. For example, if you often
reference a table with certain columns as SArgable values (eg, Where
ActiveStatus=1 And PrintedStatus=0), you should consider whether there is
any benefit having both statuses in the same composite index. Only knowing a
likely distribution of your data will allow you to make an informed
decision. Sadly a lot of IT managers love to say "it could be any number of
rows" which doesn't help! It's one of those times when the DBA should be
speaking directly with the business.
For small databases you probably won't notice any problems when the auto
create/update is switched on.
Kind Regards, Howard
Auto create & load in SQL table
Hi does anyone know how to create a sql table and then import a list by just clicking on a button to call a procedure?
CREATE TABLE clients(ClientID VARCHAR(5), ClientName VARCHAR(30), PRIMARY KEY (ClientID));
LOAD DATA LOCAL INFILE 'C:/client.csv' INTO TABLE clients
LINES TERMINATED BY '\r\n';
You can create a stored procedure that create the table if it doesn't exist, and use 'BULK INSERT' to import the data from a file into the table, specifying FIELDTERMINATOR and ROWTERMINATOR, for example:
CREATE PROC sp_ImportData @.filename varchar(200)='C:\client.csv'
as
if exists(select * from sysobjects where name='clients')
drop table clients
exec('CREATE TABLE clients(ClientID VARCHAR(5) PRIMARY KEY, ClientName VARCHAR(30))')
exec('bulk insert clients from'''+@.filename+'''
with FIELDTERMINATOR = ''\r'', ROWTERMINATOR = ''\r\n''')
go
Sunday, March 25, 2012
Authentication question
I have a question about authentication with SQL 2005. I want to create a
group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
Would users of the AD group then be able to authenticate to the SQL Server
because the group is in SQL Server or not?You need to connect to your instance and create a login principal from the
Security for the mentioned Windows Group and map it to whatever resource
they need to reach.
To achieve this, your SQL Server server also has to be joined to the
mentioned domain.
--
Ekrem Önsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uTlojAEAIHA.536@.TK2MSFTNGP06.phx.gbl...
>I have a question about authentication with SQL 2005. I want to create a
>group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
>Would users of the AD group then be able to authenticate to the SQL Server
>because the group is in SQL Server or not?
>
group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
Would users of the AD group then be able to authenticate to the SQL Server
because the group is in SQL Server or not?You need to connect to your instance and create a login principal from the
Security for the mentioned Windows Group and map it to whatever resource
they need to reach.
To achieve this, your SQL Server server also has to be joined to the
mentioned domain.
--
Ekrem Önsoy
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uTlojAEAIHA.536@.TK2MSFTNGP06.phx.gbl...
>I have a question about authentication with SQL 2005. I want to create a
>group in our Active Directory (Win 2003) and make it a user in MS SQL 2005.
>Would users of the AD group then be able to authenticate to the SQL Server
>because the group is in SQL Server or not?
>
Sunday, March 11, 2012
Auditing SQL Server users
I am trying to create a sql script that will check the database instance for any new objects that are created in any database on my system. These objects I want to audit are users, tables, databases, stored procs, etc.. I also want the script to email me and write the information to a text log file. Thanks in advance for any help.Not sure if that can be achieved. Wh don't you run an audit trace|||That's what Profiler is for. In fact, if properly scripted, it can gather whatever info you want and store it in a database, so you don't need to keep it in a text file. And your email notification can be limited to just something like "A new entry was added to the AUDIT table for USER_CREATE event." You can create a trigger on that AUDIT table and have either SQLMail or CDO/XPSMTP send you this short notice. And then you can decide whether to react upon it or let it go.
Auditing Data Changes
I am using the SCD Wizard and it is working nicely. Can someone point be to an article/tuorial that would explain how you could create an "audit trail" on the items that may have been changed (type I and II)?
Basically, what I want to be able to do is run a query that tells me what data may have changed. I figured I would have to create an auditkey field in my table which would then link the key to the change detail?
The idea behind type 1 changes is that the changes AREN'T tracked. That's the point of type-1 SCDs. Type 2s ARE tracked by their very nature, so reporting on them should be easy. Type 2 records that have changed have "expired" records either indicated by some current flag or by some end date field.|||Hi Jrp210,
I know of no articles or white papers on this, although some probably exist.
In my mind, this is ETL metadata. In larger ETL, I currently build a database that tracks row counts for the source data and then for different types of changes (including no change). It provides a the means for a quick "sanity-check" at the end of each load: I add up the rows coming out of the SCD Wizard outputs and see if this total matches the rows going in.
If I'm understanding you, you would like to take this to another level and store enough information to identify the rows that have changed, along with which type of change was made.
To tackle this scenario, I would probably use a small field appended to each destination dimension and fact table - tinyint would probably do the trick. If you make this field Null-able, I would start by updating this field - setting it to Null - before each load. For instance, let's say you have a table called dbo.DimStuff that looks like this:
StuffSK int identity(1,1),
StuffBK varchar(50),
StuffName varchar(50)
You could add another field:
StuffChangeType tinyint NULL
When your SSIS package starts, you could include an ExecuteSQL Task with the following statement:
UPDATE dbo.DimStuff
SET StuffChangeType = NULL
This would set all the StffChangeType values to Null prior to executing the load. Then you could add a Derived Column to each output of the Slowly Changing Dimension Wizard that adds a column named StuffChangeType and assigns an appropriate value (maybe 1 for updates, 2 for historical, 3 for new, etc.)
This would add change type data for each row changed - and let you know which rows were not changed.
You could then execute a query like:
SELECT StuffBK, StuffChangeType
FROM dbo.DimStuff
This would give you a picture of the business key and change types. You could qualify with a where clause:
WHERE StuffChangeType IsNotNULL
to only get changes (and change types) since the last load.
You could also do a nice summary with a query like:
SELECT StuffChangeType,Count(*)AS'ChangeTypeCount'
FROM dbo.DimStuff
WHERE StuffChangeType IsNotNULL
GROUPBY StuffChangeType
Hope This Helps,
Andy
|||But Andy, if you want to track Type 1 changes, make them Type 2. When using Type 2 SCDs, use begin and end dates to identify current and historical rows. The auditing is done for you by the nature of SCDs. That's the point of them in the first place. If you want to keep track of which LOAD processed the change, add another column, SessionKey, or something like that.|||Hi Phil,
I think he's asking for something different from SessionID, and for something more than just Type 2 tracking. To me, it sounds as if he has a business requirement to update certain data - or perhaps lacks a business requirement to track changes on that data - and still wants to track the fact that it changed. I don't think SCDs cover that, and I think the solution I offered does. It's not the only way, but I think it will accomplish what he's after.
If not, perhaps it will help someone else in the future!
Thanks,
Andy
|||Along the path you're thinking Andy, the Kimball Webcast on the first page of this forum (towards the top) talks about auditing. In there, an audit entry is recorded and an identity value is returned that is used within the applicable pieces within SSIS. This value would correspond to the "session" of this load, for this package, etc... Talks about row counts, and other things. Very valuable.|||Hi Phil,
Yep - that's good stuff.
I don't think that's what the original question was about, but it's definitely good stuff.
Thanks!
Andy
|||Thanks for the responses. The SCD does do what it is intended to do but I probably should clarify a bit.
Perhaps an example will be the best way to explain.
For the sake of the example I have the following fields in my table:
CompanyKey
CompanyName
CompanyTicker
StartDate
EndDate
The SCD is setup to create historical changes for the name and ticker. This is done with StartDate and EndDate fields. Phil, you are right that I will have a date in the EndDate field if a change comes through for a CompanyName or CompanyTicker. But what I don't know unless I compare the two rows is what exactly changed. Now this is fairly easy to eyeball given this simple scenario. But in my real life scenario I have many fields that could have changed and its not easy to tell which one(s) did. I would like to create a way to determine which of the fields did change.
Hope this makes sense.
Auditing changes to Scheduled Jobs
Hello,
Is there a way that I can create an audit trail of changes that are made to scheduled jobs in SQL Server 2000?
Thanks
This question is not directly related to SSIS, so I would try posting in the SQL Server Tools General forum to get more responses.
Bob
Thursday, March 8, 2012
auditing
I have another question.
I want to create audit table with columns (userID, date, tableAffected,
ColumnAffected).
This table should have data from tables that I want to trace. It easy to
collect data about time and users, but I don't know how to collect data abou
t
which table and which column is affected.
So, if i have table employees employeeID,Lastname,Firstname I wolud like to
have 3 record in my Audit table:
uros; 11/11/2005; employees; EmployeeID
uros; 11/11/2005; employees; Lastname
uros; 11/11/2005; employees; Firstname
Do you have any suggestions?Hi,
I wat to give my suggestion here , there might be better suggestions pouring
for this prob. so keep checking,
Create trigger for all ur tables and in triggers use Updated(col_name) to
check whether column got updated or not.
--
Vishal Khajuria
9886170165
IBM Bangalore
"uros" wrote:
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||hi Uros,
You can design a SQL server trace using sql profiler
and save the trace to a file.
for optimum performance. you can run a trace
on another machine which is the standard way of profiling sql server.
in this way your auditing doesn't disturb
the production server
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"uros" wrote:
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||Uros,
I have this design working except I have OldValues and NewValues columns
instead of ColumnAffected. These two columns are for recording affected
columns names and values in an xml style. This allows for having just one
row per action. In addition I have EventType to tell update from insert from
delete and RowID column to link back to the original table record. The work
of writing to the audit table is done in a universal trigger that can be
dropped into a table design with no modifications. In that trigger the
following is user to get the table name:
select object_name(parent_obj)
from sysobjects
where id = @.@.procid
and columns_updated() function output is parsed to get to the columns
affected.
Ilya
"uros" <uros@.discussions.microsoft.com> wrote in message
news:0D4071F5-7445-43E1-B904-B4F43DB020FF@.microsoft.com...
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data
about
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like
to
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?
I want to create audit table with columns (userID, date, tableAffected,
ColumnAffected).
This table should have data from tables that I want to trace. It easy to
collect data about time and users, but I don't know how to collect data abou
t
which table and which column is affected.
So, if i have table employees employeeID,Lastname,Firstname I wolud like to
have 3 record in my Audit table:
uros; 11/11/2005; employees; EmployeeID
uros; 11/11/2005; employees; Lastname
uros; 11/11/2005; employees; Firstname
Do you have any suggestions?Hi,
I wat to give my suggestion here , there might be better suggestions pouring
for this prob. so keep checking,
Create trigger for all ur tables and in triggers use Updated(col_name) to
check whether column got updated or not.
--
Vishal Khajuria
9886170165
IBM Bangalore
"uros" wrote:
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||hi Uros,
You can design a SQL server trace using sql profiler
and save the trace to a file.
for optimum performance. you can run a trace
on another machine which is the standard way of profiling sql server.
in this way your auditing doesn't disturb
the production server
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"uros" wrote:
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data ab
out
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like t
o
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?|||Uros,
I have this design working except I have OldValues and NewValues columns
instead of ColumnAffected. These two columns are for recording affected
columns names and values in an xml style. This allows for having just one
row per action. In addition I have EventType to tell update from insert from
delete and RowID column to link back to the original table record. The work
of writing to the audit table is done in a universal trigger that can be
dropped into a table design with no modifications. In that trigger the
following is user to get the table name:
select object_name(parent_obj)
from sysobjects
where id = @.@.procid
and columns_updated() function output is parsed to get to the columns
affected.
Ilya
"uros" <uros@.discussions.microsoft.com> wrote in message
news:0D4071F5-7445-43E1-B904-B4F43DB020FF@.microsoft.com...
> I have another question.
> I want to create audit table with columns (userID, date, tableAffected,
> ColumnAffected).
> This table should have data from tables that I want to trace. It easy to
> collect data about time and users, but I don't know how to collect data
about
> which table and which column is affected.
> So, if i have table employees employeeID,Lastname,Firstname I wolud like
to
> have 3 record in my Audit table:
> uros; 11/11/2005; employees; EmployeeID
> uros; 11/11/2005; employees; Lastname
> uros; 11/11/2005; employees; Firstname
> Do you have any suggestions?
audit UpLoad function
I'd like to create an audit report identifying all UpLoads that have been
done. Is it possible to identify this from the ReportServer or
RSExecutionLog databases?
Thanks
--
Rickselect * from catalog where type = 3
all the uploaded files have type 3.
Amarnath.
"Rick" wrote:
> I'd like to create an audit report identifying all UpLoads that have been
> done. Is it possible to identify this from the ReportServer or
> RSExecutionLog databases?
> Thanks
> --
> Rick|||The Type column in the Catalog table appears to be the object type:
1 Folder
2 Report
3 Resource
4 Linked Report
5 Data Source
I'd like to track each time a report (Type = 2) has been Uploaded.
Thanks,
--
Rick
"Amarnath" wrote:
> select * from catalog where type = 3
> all the uploaded files have type 3.
> Amarnath.
> "Rick" wrote:
> > I'd like to create an audit report identifying all UpLoads that have been
> > done. Is it possible to identify this from the ReportServer or
> > RSExecutionLog databases?
> >
> > Thanks
> > --
> > Rick|||Rick,
You want to find out the deployed report or uploaded because type =2 will
give you deployed report and from "report manager" when you upload then it is
option type =3
Amarnath
"Rick" wrote:
> The Type column in the Catalog table appears to be the object type:
> 1 Folder
> 2 Report
> 3 Resource
> 4 Linked Report
> 5 Data Source
> I'd like to track each time a report (Type = 2) has been Uploaded.
> Thanks,
> --
> Rick
>
> "Amarnath" wrote:
> > select * from catalog where type = 3
> > all the uploaded files have type 3.
> >
> > Amarnath.
> >
> > "Rick" wrote:
> >
> > > I'd like to create an audit report identifying all UpLoads that have been
> > > done. Is it possible to identify this from the ReportServer or
> > > RSExecutionLog databases?
> > >
> > > Thanks
> > > --
> > > Rick
done. Is it possible to identify this from the ReportServer or
RSExecutionLog databases?
Thanks
--
Rickselect * from catalog where type = 3
all the uploaded files have type 3.
Amarnath.
"Rick" wrote:
> I'd like to create an audit report identifying all UpLoads that have been
> done. Is it possible to identify this from the ReportServer or
> RSExecutionLog databases?
> Thanks
> --
> Rick|||The Type column in the Catalog table appears to be the object type:
1 Folder
2 Report
3 Resource
4 Linked Report
5 Data Source
I'd like to track each time a report (Type = 2) has been Uploaded.
Thanks,
--
Rick
"Amarnath" wrote:
> select * from catalog where type = 3
> all the uploaded files have type 3.
> Amarnath.
> "Rick" wrote:
> > I'd like to create an audit report identifying all UpLoads that have been
> > done. Is it possible to identify this from the ReportServer or
> > RSExecutionLog databases?
> >
> > Thanks
> > --
> > Rick|||Rick,
You want to find out the deployed report or uploaded because type =2 will
give you deployed report and from "report manager" when you upload then it is
option type =3
Amarnath
"Rick" wrote:
> The Type column in the Catalog table appears to be the object type:
> 1 Folder
> 2 Report
> 3 Resource
> 4 Linked Report
> 5 Data Source
> I'd like to track each time a report (Type = 2) has been Uploaded.
> Thanks,
> --
> Rick
>
> "Amarnath" wrote:
> > select * from catalog where type = 3
> > all the uploaded files have type 3.
> >
> > Amarnath.
> >
> > "Rick" wrote:
> >
> > > I'd like to create an audit report identifying all UpLoads that have been
> > > done. Is it possible to identify this from the ReportServer or
> > > RSExecutionLog databases?
> > >
> > > Thanks
> > > --
> > > Rick
audit trail...
Hi...
A much lamented question, I guess..
A much lamented question, I guess..
I'm trying to create a simple audit trail.
log the changes to an SQL 2000 table, so that they are written into a
mirror table. The entire record, only the updated one, i.e. if say
only one field changes, the audit table will be inserted with one
record that has one field changed. if the record has been deleted, it
still will be written.
I'm not worrying about additional fields to the audit table containing
descriptive flags of what action took place yet. I just want the
mirror image for starters.
I got the script of the 'create table' off Query analyzer. created the
audit table.
the trigger looks like this:
CREATE TRIGGER dt_tbl1_audit
on tbl1
for insert, update, delete
AS
insert into tbl1_audit
select * from inserted
the table has about 50 fields or so, so I tried to make do with *'s.
didn't work, so I tried copying and pasting the explicit list of field
names
instead (though I'm not sure why it needs that if the two tables are
identically structured).
in either case, if I update any field on the audited table, I get this
error:
(after getting the warning that the results may take a long time to
process etc, the original table has over 100,000 rows)
"another user has modified the contents of this table or view,
the database row you are modifying no longer exists in the database
database error: insert error:
column name or number of supplied values does not match table
definition"
I'm not sure what's wrong, the two tables are identical (I copy pasted
the create table script with no changes). no other users except me on
this database.
i've removed all constraints and indexes from the audit table.
thanksHi
It seems that you are having problems with posting!!!
If speed is important you should think about keeping simple copies of the
inserted and deleted tables (possibly with other columns such as a timestamp
and user name) as this will mean that the trigger the least processing and
not causing you transactions to be open for a elongated period. The work of
resolving what columns have been updated can then be done either when
reporting or at a more convenient time.
If you wish to do this processing withing the trigger check out Books online
under the "Create Trigger" topic, you can find information about using the
UPDATED() and COLUMNS_UPDATED() functions and example of how to use them.
Another alternative method is to use a log file reading product such as
those from Lumigent (Lumigent Log Explorer) http://www.lumigent.com/ or PI,
http://www.logpi.com
and process the information in the log files.
HTH
John
"Me" <heruti@.lycos.com> wrote in message
news:2d4c3262.0411261512.6d8072aa@.posting.google.c om...
> Hi...
> A much lamented question, I guess..
> I'm trying to create a simple audit trail.
> log the changes to an SQL 2000 table, so that they are written into a
> mirror table. The entire record, only the updated one, i.e. if say
> only one field changes, the audit table will be inserted with one
> record that has one field changed. if the record has been deleted, it
> still will be written.
> I'm not worrying about additional fields to the audit table containing
> descriptive flags of what action took place yet. I just want the
> mirror image for starters.
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
>
> thanks|||Me (heruti@.lycos.com) writes:
> I got the script of the 'create table' off Query analyzer. created the
> audit table.
> the trigger looks like this:
> CREATE TRIGGER dt_tbl1_audit
> on tbl1
> for insert, update, delete
> AS
> insert into tbl1_audit
> select * from inserted
>
> the table has about 50 fields or so, so I tried to make do with *'s.
> didn't work, so I tried copying and pasting the explicit list of field
> names
> instead (though I'm not sure why it needs that if the two tables are
> identically structured).
Depends on what columns there are in the tables. If you have an IDENTITY
colunm in the source table, the corresponding table in the audit table
cannot have the IDENTITY property. And if there are timestamp columns,
you would have to make them binary(8) in the target table.
In any case, some sort of a primary key for the target table would be a good
idea.
> in either case, if I update any field on the audited table, I get this
> error:
> (after getting the warning that the results may take a long time to
> process etc, the original table has over 100,000 rows)
So where does this warning come from?
> "another user has modified the contents of this table or view,
> the database row you are modifying no longer exists in the database
> database error: insert error:
> column name or number of supplied values does not match table
> definition"
> I'm not sure what's wrong, the two tables are identical (I copy pasted
> the create table script with no changes). no other users except me on
> this database.
> i've removed all constraints and indexes from the audit table.
Well, we don't even know the definition of the tables, so how could we
tell what is going on?
Do you get this error when you perform an update from Query Analyzer? If
so, can you cut and paste the complete error message? The error message
should include procedure name and line number where the message appears.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||yes, I did have trouble posting, I do apologize..
IE6 reported some sort of 404 error when I clicked submit in the
dejanews post. I assumed it didn't & tried several dozen times until I
accidentally discovered that it did post (dejanews says it takes
several hours to post, so traditionally I wouldn't have discovered
this, but this time I stumbled on another site, sort of a gateway to
google groups, which showed my post immediately... seems useful.
http://news-reader.org/comp.databases.ms-sqlserver/
re the audit, I did get the code to work both ways, with *'s notation
and also with detail listing of all the fields. so this post is
generally for the benefit of other befuddled customers on my trail..
The following trigger works. audits updates/inserts on the table
tblSource, into the log table tblSource_Audit which has the same
structure plus the two fields 'LogActionType' (varchar 10) and
'LogDate':
CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
INSERT INTO tblSource_audit
select 'insert/upd',GetDate(),
* from Inserted ins
GO
Still, I thought it would be safer (future maintenance wise, so the
trigger won't break if fields are added to the source table)
to convert the whole thing into detailed column notation (too long to
list here), and Later add a little condition code to it that would
post 'insert' and 'update' strings identifying the two operations and
not the combined string above:
CREATE TRIGGER dt_insupd
on tblSource
for Insert,Update AS
Declare @.ActionType VARCHAR(10)
Declare @.DeleteCnt int
set @.DeleteCnt = (select count(*) from deleted)
if @.DeleteCnt = 0
begin
set @.ActionType = 'Insert'
end
ELSE
set @.ActionType = 'Update'
INSERT INTO tblSource_audit
select @.ActionType,GetDate(),
* from Inserted ins
GO
Thanks everyone for your help. Any more comments, of course, welcome.
Audit Tables and triggers
Dear Group,
I would like to create an audit table that is created with a trigger that
reflects all the changes(insert, update and delete) that occur in table.
Say I have a table with
Subject_ID, visit_number, dob, weight, height, User_name, inputdate
The audit table would have .
Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
edit_action, edit_reason.
Where the edit_action would be insert, update, delete; the edit_reason would
be the reason given for the edit.
Help with this would be great, since I am new to the world of triggers.
Thanks,
JeffJeff Magouirk (magouirkj@.njc.org) writes:
> I would like to create an audit table that is created with a trigger that
> reflects all the changes(insert, update and delete) that occur in table.
> Say I have a table with
> Subject_ID, visit_number, dob, weight, height, User_name,
> inputdate
> The audit table would have .
> Subject_ID, visit_number, dob, weight, height, User_name, inputdate,
> edit_action, edit_reason.
> Where the edit_action would be insert, update, delete; the edit_reason
> would be the reason given for the edit.
> Help with this would be great, since I am new to the world of triggers.
If you need to do to this on a broad scale, consider 3rd-party solutions.
Two that I usually recommend - although I've used none of them myself -
is SQLAudit from Red Matrix and Entegra from Lumigent. SQL Audit is
based on triggers, Entegra works from the transaction log.
But for a one-shot you could do:
CREATE TRIGGER tbl_audit_tri ON tbl FOR INSERT, UPDATE, DELETE
IF @.@.rowcount = 0
RETURN
IF EXISTS(SELECT * FROM inserted)
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ...
CASE WHEN EXISTS (SELECT * FROM deleted)
THEN 'UPDATE'
ELSE 'INSERT'
FROM inserted
END
ELSE
BEGIN
INSERT logtable (subject_id, ... edit_action)
SELECT subject_id, ..., 'DELETE'
FROM delete
END
As you see I have left out edit_reason. This is because I don't know
what you mean with "edit_reason", and anyway it sounds like something
that can be quite difficult to get hold of from the trigger.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)