SQL Profiler - How Does It Affect SQL Performance?
Jun 23, 2006
Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.
Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.
Any help I can get would be extremely appreciated.
Thanks.
View 4 Replies
ADVERTISEMENT
Aug 7, 2001
We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Any thoughts ?
View 1 Replies
View Related
May 20, 2008
Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results
SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
...
View 3 Replies
View Related
May 4, 2001
I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
View 1 Replies
View Related
Mar 27, 2006
I have two instances installed on my server. I have one database on the default instance and two very large reporting databases on the other instance.
Will having the default instance running affect the performance of the non-default instance where my reporting databases are?
View 1 Replies
View Related
Jul 23, 2005
I'm considering adding domain integrity checks to some of my database tableitems. How does adding such constraints affect SQL Server performance? Forexample, I have a simple constraint that restricts a couple of columns tohaving values within the values assigned in my application by anenumeration:(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=2))This enforces domain integrity for two enumerations having values 0, 1, 2, 3and 0, 1, 2 in the application. Is this an efficient way of performing suchchecks? What are the pitfalls of domain integrity checking?ThanksRobin
View 1 Replies
View Related
May 23, 2008
We have more than 2000 tables in the database. Can existence of so many tables affect the performance on SQL SERVER 2005?
View 1 Replies
View Related
Jul 20, 2007
Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.
Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.
Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?
If so, would moving this new table into it's own database be recommended?
Thanks
Rod
View 1 Replies
View Related
Mar 8, 2004
Hi,
We have a poorly performing SQL 2000 db. i have just defragged ( the HD, not indexes, these are done daily via SQL Agent) the data files of our server and have not found any improvement in response.
I have now got into using SQL profiler to analyse the server performance. in the results that the trace is returning there are some huge (REALLY BIG) values for the duration and cpu values but these rows have no textdata value returned (ie it is null)
why is this? for these rows, the reads and writes columns are also high.
if these rows are what is taking the cpu's time then how can i identify what the server is doing to make any changes?
any thoughts on what other values i might trace or what action i can take to find the slow down cause?
in performance manager the processors (dual Xeons) are rarely dropping below 60%.
thanks in advance
fatherjack
View 2 Replies
View Related
Nov 9, 2007
I am running a trace and one of the columns is start time
I want to import corresponding performance log data in the profiler
It is a new feature in 2005
however this option is disabled in the profiler
this option is at
File -> Import performance Data
Please advice on how to enable this option
thanks in advance
View 5 Replies
View Related
Jul 20, 2005
Greetings,I have been attempting to develop a useful and functional template fordatabase tracing/profiling that will enable me to collect metrics forperformance tuning. The database is used as an OLTP database as well asrunning reports. Below is a list of my trace properties and data columns.I would be interested to see other examples and strategies for the Profiler.thanxPerformanceExecution planSecurityAudit LoginAudit LogoutSessionsExisting ConnectionStored ProceduresRPC: CompletedTSQLSQL:Batch completedDATA COLUMNSEvent classtextdataapplication nameNTUsernameLoginNamesCPUreadwritedurationclient proc idSPIDStarttime
View 3 Replies
View Related
Mar 19, 2002
every morning I have 7-10 identical messages in error log
1.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2.
Error: 15457, Severity: 0, State: 1
3.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
It is standby server with custom log shipping and DTS transfering logins every 15 min.
What could cause this error ?
View 1 Replies
View Related
Dec 8, 1998
Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
View 2 Replies
View Related
Aug 6, 2004
Hi all,
does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?
Thanx
View 1 Replies
View Related
Jun 10, 2004
I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?
View 6 Replies
View Related
Aug 7, 2007
hello,
I have a query that insert insert into new table , and then i select from this table,
if i add ORDER BY in the INSERT INTO script , does it affect the speed of the SELECT
i have big table that take about 70 sec
View 14 Replies
View Related
Nov 16, 2006
i have a table such sa below:
Name1, Name2, Name3, Nam4, C1, C2,.., C100
and in this table, i have found index for Name1-Nam4,
i don't why sql below is very slow?
select
Name1, sum(C1), ...., Sum(C100)
from
(
select
Name1, Name2, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, sum(C1) as C1, ...., Sum(C100) as C100
from
(
select
Name1, Name2, Name3, Name4, C1, ...., C100
from
My_Table
group by Name1, Name2, Name3, Name4
) as T
group by Name1, Name2, Nam3
) as T
group by Name1, Name2
) as T
group by Name1
Does 'Group By' affect the speed of query?
View 1 Replies
View Related
Jan 2, 2007
If the mirror server goes down for any reason, then what happens to client transactions against the principle?
Will any client transactions fail?
Thanks in advance
View 3 Replies
View Related
Nov 28, 2007
Dear all,
In Flat File Source properties windows there's Preview node, when we check that node there's an option to skip the data in how many rows. Is it affect the result ?
Best regards,
Hery
View 3 Replies
View Related
Jan 30, 2008
Hi,
I have a column in table which tells me whether Tax should be calculated on a price. This is stored in a column called taxIncluded. I have a 'price before tax' column e.g. grossPrice. I want to calculate the price after tax e.g. netPrice if the taxIncluded column is set to 1. How do I form my sql statement to test whether the taxIncluded is set to 1 & therefore add the tax at say 20%.
e.g.
id name grossPrice taxIncluded netPrice <--- calculated within SQL
-- ------------- ------------------- ---------------- -------------
1 Product1 10.00 1 12.00
2 Product2 20.00 0 20.00
View 7 Replies
View Related
Mar 17, 2001
Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
Thanks
View 1 Replies
View Related
Mar 19, 2008
Hi,
I had a report (.rdl) using sql reporting services in sql server 2005, where it was running quite good.
I have just installed sql server 2005 sp2. After that, when I run the report through report viewer, the result of the report contains the first record of the dataset keep on repeating for all other records. If I run the dataset the results are correct,but if I preview it, then the first record alone repeating for all other records.
I feel that sp2 might cause this rendering problem. Any suggestion please?
Murali.
View 1 Replies
View Related
Jun 5, 2007
Hello,
Does Service Pack 2 affect client tools? In other words, if you have a client machine with just the tools installed (management studio, BIDS, etc) do I need to run the SP2 package on this client as well?
Regards, Jeroen
View 3 Replies
View Related
Apr 16, 2008
Hi,
I am developing a report that should be localized dynamically. The report will have both arabic and western users. Therefore I need to use the direction property of some single text boxes, which works fine. But when setting the direction property to RTL for a table it has no affect!
You would assume that the whole tables content should be "reversed" and also the text in the table went from right to left. But no.
When viewing the report in IE and right clicked and click "View Source" you can see that the table do not have a direction style set, even though I set the property on the table in design mode.
I am going crazy about this issue! Could this be a MS bug?
I really need some help here...
PS. I have tried numerous of combinations of the different International properties to get the direction property to work - with no luck.
View 1 Replies
View Related
Aug 3, 2015
Could a simple update statement on a user database ever caused space usage in tempdb? Assuming the update statement fires no triggers and not using any temp tables?
IE:
User DatabaseA
Update TableX
Set col1 = X
Reason I ask is tempdb filled up and the only thing I could see running at that time was the update statement.
View 8 Replies
View Related
Jan 31, 2007
Hi below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it. DECLARE @t TABLE(the_data CHAR(73)) SET DATEFORMAT dmySET NOCOUNT ON INSERT INTO @tSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'UNION ALLSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'UNION ALLSELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+ '09 01 2003' SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine, date1_textFROM--derived table - selecting relevant substring(SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_textFROM @t)AS der_t date_1_prob date_1_ok_ish date_1_fine date1_text----------------------- ----------------------- ----------------------- ----------NULL 2002-01-08 00:00:00 2002-01-08 00:00:00 08 01 2002NULL 1900-01-01 00:00:00 1900-01-01 00:00:00 NULL 1900-01-01 00:00:00 NULL XXXXXXXXXX Can anyone explain the result in the first row first column? Thanks
View 8 Replies
View Related
Aug 16, 2007
We have an Access application using Jet. I added some new indexes yesterday and now they are being blamed for poor Access application performance. I then dropped the new indexes. The poor performance continued until the Access application was re-linked to the SS2000 database. Then things returned to normal.
Question, does Access/Jet persist SqlServer schema info in its MDB (or elsewhere?) I am told that the MDB is copied from a share to the local PC where it grows during its use. Some people are saying the MDB persists schema info about the SS2000 schema which influences how Jet accesses the SS2000 database. Is that true? Is there a link where I can read about this? I am a dba and am not an Access developer . . .
Thanks!
Michael
View 4 Replies
View Related
May 31, 2007
With SQL2005 SP2, we are seeing that when auto stats run on one or more indexes of a large table (1.5M rows), then immediately the stored proc using that table starts acting as if the query plan is no longer any good. This causes a drastic slowdown in response time and a corresponding increase of table reads to get the data. E.g, the next execution of the procedure after the auto stats kick in goes from 355 reads to 755000 reads (as depicted by Profiler). Generally, there are about 25 people using the DB at any one time. They connect through a mid-tier VB component.
I tried adding WITH RECOMPILE to the stored proc in question, but that caused almost all executions to run at the higher number. I thought that the WITH RECOMPILE hint would create a new query plan for each execution of the procedure and that plan would the the latest and greatest. Perhaps it did, but most users got stuck with the higher number of reads anyway. After taking the hint out, everyone went back to getting the 335 number and quick response times.
What we are wrestling with is that when those auto stats hit, it really messes up everyone until we manually recompile the procedure. Daily we delete all records in the table that are over 45 days old, so the table stays pretty much the same size. We also set the recompile flag to cause a new plan to be generated that will reflect the smaller amount of data. Should we also run a stats update before recompiling the procedure? Profiler has been very helpful in capturing what is going on, so I think I have a good handle on that. However, I don't understand why WITH RECOMPILE produced a messed up plan for everyone. The compile itself seems to take only 1 ms when done from the query screen.
View 11 Replies
View Related
Jul 22, 2015
In a recent attempt to keep the size of my transaction log files down I altered the schedule of my SQL Server log backups from running every 15 minutes from 07:00 to 19:00 to run every 15 minutes.My company also uses a Dell AppAssure application to also take backups. The backups are of the entire drives so I don't this will affect the size of my SQL log files but I did notice that AppAssure has a tick box to truncate the SQL logs so it made me wonder that it could affect the size of my log file. The Appassure backups currently run every 15 minutes from 07:00 to 19:00. I'm wondering if I would be able to maintain my log files at a smaller size if I ran this every 15 minutes from 07:00 to 07:00.
View 2 Replies
View Related
Mar 19, 2008
With the Synonym, I was encouraged to separate my db to several smaller dbs, like base,dynamic,static and security. Now I am trying to use mirroring, I see it may cause problem, I think I need mirror all them to another server. My question is when the server is down, will all db switch to mirror server in the same time? And one can manually set which db is the principal db, but in my case, it will not work if principal server of all four dbs are not the same.
Any thought?
thanks
View 3 Replies
View Related
Dec 8, 2006
Hi,
We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time.
Is that copy process affect production time out?.
Thanks,
View 1 Replies
View Related
Oct 16, 2007
for some filegroups, we are turning readwrite back on periodically to trickle some data into an archive. This lasts a few seconds and then we turn readonly back on. What is the impact of doing this while a query is running on the archive?
View 1 Replies
View Related
Aug 13, 2015
In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name?
2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever?
3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database?
4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
View 3 Replies
View Related
