has anybody investigated the cost of turning on AUDITING for the SQL servers? I am talking about enabling the entire C2 Security Audit mode. How much impact the auditing has on the database performance?
Is auditing for SQL 2005 any better, meaning less impact on performance?
Any sample, test, and/or numbers to support the arguments?
Please share any findings that you have or know. Thanks
Hi, I need to write some T-SQL scripts to perform a database audit of several SQL Server 2000 databases that tracks all superuser logins and access to tables. I can do this in Oracle but I am lost with MS SQL Server. Can anyone point me in the right direction? Thanks!
View 6 Replies View RelatedOn the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!
View 2 Replies View RelatedI need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?
View 9 Replies View RelatedI wrote some code to insert some data into a table, if the record already exists overwrite, etc. I've never done something like this so
I am going to bang my head against that for a while. But when I did an execution plan I got
Index Seek Cost : 105 %
How do I get more than 100% and what does that mean?
Thanks,
Jim
hi there,
i have a site that has high traffic on it...and ofcourse there is search on the site...
one of the search options is to search by name, so i use a stored procedure as following :
select StID,StName from StudentTable where StName like '%' + @SearchField + '%'
ofcourse it works perfectly, the problem that it has a really high cost on the sql server..
so is there any way i can search for the name and using the '%' with lower cost, noting that the name is of type varchar(100)
Thanks in advance
Mahmoud Manasrah
OK, this should be an easy one but my brain isn't quite working right now.
I have a table, we'll call Table1 like so:
ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00
And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:
SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID
and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.
Thanks,
Is there a way that we can figure out what the cost of a SQL query is via jdbc ? any command etc?
View 1 Replies View RelatedI am querying a database that has hours worked listed by pay rate. I want to return sum of hours per pay rates I have been given but hours are stored in multiple columns. So If I run the below query for one worker it returns.
HoursBill_rate_1 Hours_2Bill_rate_2
34.2610.26 0.000.00
5.7410.26 5.689.67
I want to report to the business:
40hrs 10.26
5.68 9.67
The query I have written is below:
Select Sum(Hours_1) As Hours,Bill_rate_1,Sum(Hours_2) as Hours_2,Bill_rate_2--,Hours_3,Bill_rate_3
From Valid_Timesheets
Where (Department = '938' and tax_period = 3 and tax_year = 2015 AND PERSONNEL_REF = '991A001198') and
((Bill_Rate_1 = £10.26 or
Bill_Rate_1 = £9.67 or
Bill_Rate_1 = £8.27 or
[Code] ....
Hi,
Does anyone know how much storage space is required in SQL Server for a char(1) compared to a varchar(1)?
If I have a table with 14 varchar(1) fields and 60652902 rows, how much is this overhead costing me in wasted space?
Answers on a postcard!
Thanks,
Richard.
I have 2 different queries which produce same result. I want to know which querry is better and why?
The query is used to display the employee details who is handling the maximum number of project.
Queries are the following
Query A
Code Snippet
SELECT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
COUNT(LUP_EmpProject.Empid) AS Number_Of_Projects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.Empid=EmployeeDetails.Empid
GROUP BY EmployeeDetails.FirstName+' '+EmployeeDetails.LastName,
LUP_EmpProject.Empid
HAVING COUNT(LUP_EmpProject.Empid)>0
AND COUNT(LUP_EmpProject.Empid)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub)
Query B
Code Snippet
SELECT LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName AS EmpName,
COUNT(*) AS NumberOfProjects
FROM LUP_EmpProject
INNER JOIN EmployeeDetails
ON LUP_EmpProject.EmpID = EmployeeDetails.EmpID
GROUP BY LUP_EmpProject.EmpID,
EmployeeDetails.FirstName + ' ' + EmployeeDetails.LastName
HAVING COUNT(*)=(SELECT
MAX(Number_Of_Projects)
FROM (SELECT COUNT(LUP_EmpProject.Empid) Number_Of_Projects
FROM LUP_EmpProject
GROUP BY LUP_EmpProject.Empid)AS sub
Please Help!!!!!!!!!!!!!!!!1
I've converted most SQL Server databases to unicode since the AS 400 stores data in unicode (UTF-16). So far the only difference I see is that running queries to text in Unicode takes a lot longer, and UTF-16 files are 4x as large as Cp1252 files.
Any way to get the file sizes / query times of old with Unicode?
I wanna used derived fields to improve select performance of my system.
How can I calculate the trigger cost in the system?
If I run sample query below against an adventureworks database
where can I find query cost?
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;
Does estimated subtree cost for in the actual execution plan (when right click SELECT operator in the execution plan) is considered query cost?
hello,
Does anybody knows how to calculate cost of query ? Any help link or something.
--kneel
After lots of testing I have found that re-using dialogs improves performance significantly, however I cannot fathom out what is the cost of re-using them. Performance does not come for free hene why I feel there is a cost. Can anyone shed any light on the cost of re-using dialogs? Also why should you re-cycle them? What benefits does recycling dialogs have?
Many thanks
David
I have been told that many ETL tools used to cost hundred of thousands of dollars. Is this true or were they referring to the ETL and Analysis tools, together? Can anyone provide some actual product names and estimated "costs" that hits these marks?
TIA,
barkingdog
It seems to me there are two common strategies for doing DB audit trails via a trigger:
1. On an update to a row, duplicate that row in another table with
identical rows, except for perhaps the extra columns which represent
change date and changed by. Eg. When there is an update to the Customer
table, record the changes in Customer_Audit.
2. On an update to a row, check which fields were updated. Then in a
common audit table record the table, row ID, field, previous value and
new value of the field.
I'm wondering about the pros and cons of each. More specifically, do
the pros and cons change if you are using an O/R mapper (I'm using
NHibernate.)
Some thoughts on method 1 . It seems nice for an O/R mapper, since you
could have, say, a CustomerAudit Object inherit from your Customer
object and just add the properties change date and changed by. A
problem with this is you're going to add a whole lot of objects - one
for each object which you want to audit. Another drawback is that it
could be difficult to generate a history for a particular property
which was updated. Let's say I want to see the history of changes to
the customer's status. I have to load a collection of CustomerAudit
objects (which could be costly). Then I have to iterate through them
and compare the status properties to generate a history of statuses.
This is a pretty labor instensive method if you compare it to method 2,
where the change is recorded by field, not row.
Some thoughts on method 2. It's nice since the changes are by field,
not row, which (as above) makes generating a history easy. On the other
hand, you can never have a snapshot of a particular object at a
particular point in time. Moreover, I'm not sure how foreign keys would
be handled elegantly. I record that customer.statusID changes from 3 to
6. I'd have to do a seperate join to the customerstatus table to get
meaning for 3 and 6 (which method 1 would do automatically).
Thoughts? Any preferred way to do this with an O/R mapper?
Thanks
Hello,
I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?
Any help is appreciated
Thanks
Dinesh
A few days ago I saw a article on the internet (i don't know where), which described a way for auditing all database activities (like deleting records per user etc.) Unfortunally I can't find this document. Can anyone help me with this?
View 1 Replies View Relatedhow to audit DTS ?
80 % of DTS run from
exec master..xp_cmdshell 'dtsrun ....
20 % started by server agent
I have list with 200 DTS
I want to delete dts that are not in use.
Can I use profiler or it its possible to get info from system tables?
Thank you
Hello,
I'm working on Sybase and recently started working on MS SQL Server. Can anyone guide me how to set up auditing for errors whether fatal or not?
Any help is appreciated
Thanks
Dinesh
Guys,
We looking to audit tables in the database. In the tables auditing needs to be done only on few columns not all the columns. Is there any 3rd party tool available to do it can it auditing be done on columns only with sql server 2005. Trigger doesnt work in this scenario as it works per row it does not say which column in the row was updated/inserted/deleted.
For example in the below employee table, I want to audit(history) only ssn and dob columns - all the updates/inserts/deletes made to this column should be audited.
empidempname ssndob
1John,smith 122222222 07/29/2001
Any suggestions and inputs would help
Thanks
im try to get the subtree cost (sql server 2000) back from a dynimic sql query that i build on the fly.. the sql query is:
SET SHOWPLAN_ALL ON GOselect * from mp_Contacts Where FirstName = 'Jason' --my queryGOSET SHOWPLAN_ALL OFF GOGO
the c# code is:
<code>
Decimal HoldSubTree;
// Make Connection
SqlConnection FindSubTree = new SqlConnection(Utilities.GetCRMEnvironment());
//Sql Connection
System.Text.StringBuilder SHOWPLANALLON = new System.Text.StringBuilder("SET SHOWPLAN_ALL ON");
System.Text.StringBuilder SHOWPLANALLOFF = new System.Text.StringBuilder("SET SHOWPLAN_ALL OFF");
System.Text.StringBuilder SubTreeSql = new System.Text.StringBuilder(MySqlString.ToString() + " ");
// Grab the data and fill the dataset
SqlCommand SubTreemyCommand = new SqlCommand(SHOWPLANALLON.ToString(), FindSubTree);
SubTreemyCommand.CommandTimeout = 90;
SubTreemyCommand = new SqlCommand(SubTreeSql.ToString(), FindSubTree);
SubTreemyCommand.CommandTimeout = 90;
SqlDataAdapter SubTreemyDA = new SqlDataAdapter();
SubTreemyDA.SelectCommand = SubTreemyCommand;
DataSet SubTreemyDS = new DataSet();
SubTreemyCommand = new SqlCommand(SHOWPLANALLOFF.ToString(), FindSubTree);
SubTreemyCommand.CommandTimeout = 90;
try
{
SubTreemyDA.Fill(SubTreemyDS);
</code>
and the result: it bring back the rows of data from the dynimic query instead of the subtree cost..
how could i send 3 statements to the sql database but all in the same transaction.. for example:
first the server would switch modes:
"SET SHOWPLAN_ALL ON"
then i would send it my query:
"select * from mp_Contacts Where FirstName = 'Jason' "
and then switch the server back over:
"SET SHOWPLAN_ALL OFF"
Is this possible?? ideas??
We use SQL Server 2005 and we use lots of stored procedures. Each time a stored procedure run we log the name, execution time and username so we can identify trends and long-running procedures.
I would like to add the actual CPU and IO cost of execution to this log. Is there a way to get it? This type of information is viewable in SQL Server's performance tools - so there must be some way to get it.
Can someone point me in the right direction please?
Thanks!
Hi,
We are in need of a JDBC driver (version 3) to support some autonumbering in SQLServer. We've found a couple, very expensive stuff (JTurbo & DataDirect), these drivers can be more expensive than a SQLServer license. Any other ideas/sources?
Thanks,
Bob
I have a query i have been optimizing. Now runs in about 15 minutes but was wondering if there is any way tr educe the SORT cost.
Currently the high costs left are the Table insert which is 58% and the Sort cost of 36%
The inner query below is around 400million rows and aggregates to around 15,000,000 rows)
SELECT@1 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -1, GETDATE() -1) as DATE)
SELECT@2 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -2, GETDATE() -1) as DATE)
SELECT@3 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -3, GETDATE() -1) as DATE)
SELECT@4 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -4, GETDATE() -1) as DATE)
SELECT@5 = DateKey FROM dbo.DimDate WHERE TheDate = CAST(DATEADD(WEEK, -5, GETDATE() -1) as DATE)
[code]....
I have enabled the query governor on our SQL2000 SP2 server with athreshold of 3600. Now, some of the maintenance jobs fail due to thelimit being to low (e.g. one of the user databases integrity checkfails nightly).I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' justbefore the line in the step which reads 'EXECUTEmaster.dbo.xp_sqlmaint N'-Plan etc'but it has no effect.Does anyone know how to get around this situation without usingsp_configure to change the query governor settings at a systemwidelevel?GC.
View 2 Replies View Relatedwhat does query cost(retrive to the batch) mean in execution plan?
what is the differeence between query cost :100% and 65%?
Hi all
I have got 3 columns in my table- start date,finish date and cost..in the
following format...
start_date finish_date cost
12/12/2000 20/12/2000 $2000
01/09/2000 12/10/2000 $400
Now if the month and year of the start and finish date is same, the cost
remains same...
but if the month of the two dates are different, i have to distribute the
cost between the two months by calculating the cost for the number of days
for both of the months..
but i am not able to figure out how?
i am using sql 2005 ..
my table has got about 1 million rows...
pls help.....dll is as followsGO
CREATE TABLE [dbo].[DSS](
[Service Start] [datetime] NULL,
[Service End] [datetime] NULL,
[FMIS Code] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Client NHI] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[No of Units] [float] NULL,
) ON [PRIMARY]
I have a table and the data in this table (for no rhyme or reason)is being deleted somehow. I'm looking for suggestions on how to audit this table and find out who or what process could be deleting my data.
View 3 Replies View RelatedHi,
i need to set up some security standands in sql 6.5/7 . These includes auditing login attempts success and failure, access to db objects etc. I know sql is has very limited capabilities . can anyone tell me how to implement this without using event viewer etc??