SQL 2012 :: Is Profiler Reads Column Incorrect For Parallel Plans
Aug 19, 2015
Is the SQL Server Profiler Reads Column Incorrect For Parallel Plans?
I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Does the profiler only display one thread of a parallel plan perhaps? Or something else?
View 9 Replies
ADVERTISEMENT
Aug 19, 2015
I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Clearly 12 reads is wrong. Does the profiler only display one thread of a parallel plan perhaps? Or something else?
View 1 Replies
View Related
Jul 27, 2007
Hi,
Can any of can explain, what the "Reads" column in Profiler exactly mean ? I'm not comfortable with the explanation given in BOL.
"The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution"
For the same procedure with same parameters, if the server is not loaded much, the Reads are in a few hundreds, but when there are more than 1000 concurrent users, why it is going to millions ? What other parameters affecting this reads ? And how can I reduce it ?
Environment: SQL Server 2005 64-bit Enterprise Edition on Windows Server 2003 R2 Server x64 Enterprise Edition SP2
Thanks in Advance.
Regards
Babu
View 4 Replies
View Related
Jul 23, 2005
I am running a profiler trace against a database and noticed that thereads column always shows 0. When running the same trace againstanother machine I get back values in the reads column. I took a querythat profiler reported as having 0 reads and ran in in query analyzerwtih STATISTICS IO on and confirmed that there are in fact reads:Table 'tt_cawardalloc'. Scan count 1, logical reads 8, physical reads0, read-ahead reads 1.Table 'tt_clineitem'. Scan count 10, logical reads 125208, physicalreads 1540, read-ahead reads 2995.Table 'tt_contractitem'. Scan count 32, logical reads 676, physicalreads 0, read-ahead reads 0.Table 'tt_contract2'. Scan count 3, logical reads 121, physical reads4, read-ahead reads 0.I am on SQL 2000 sp3a. Any help appreciated.Thanks!
View 8 Replies
View Related
Apr 17, 2007
I'm running the same query on two different PCs and tracing results in Profiler on my PC. When executing the query on PC1 - the total number of reads is 200000. When executing the same query on PC2 - the toal number of reads is 13000. It is almost 15 times more reads when executing query on PC2. The executed query is same on PC1 and PC2. Any reason for this?
I'm trying to analyse that query and reduce the number of logical reads as it's is too high but then I get completly different result on different PC.
Thanks.
View 5 Replies
View Related
Jun 2, 2008
I ran Sql profiler and got the following results for a stored procedure
CPU of 1078;
Reads of 125464
writes of 0
Duration of 1882
how do i interpret the above results.
also what CPU and Duration is considered high and indicating a poor performing query.
I am using SQL Server 2005. Thanks
View 4 Replies
View Related
Apr 17, 2007
I'm using an application that produce 48000000 reads for one stored procedure and 170 seconds to complete. The same procedure when executed in SQL Analyzer takes only one seconds and 10000 reads.
What is happening here? Where should I look to solve this problem?
Thanks
View 1 Replies
View Related
May 5, 2015
Just migrated application from Oracle to SQL and we are seeing alot of deadlocking and blocking. I did notice that app seems to be passing isolation level of repeatable read. Attached is a .doc of one of the deadlocks, is there a way to avoid these in the repeatable read isolation level? This example is a select with two tables, using NCI's that cover the where, and a insert doing just a clustered index insert. Is this simply try to get rid of the repeateable read if not needed, guess have to check with vendor on that or is there a way to get this to not deadlock using repeatable read?
View 2 Replies
View Related
Feb 24, 2015
We have a monitoring tool that find a query that is using most of execution time of all sessions on the server.
I located it, and found it is a data flow task in an SSIS package.
It export data from a table which has big mount of data to another database.
I know it only executes one time, but I see in the monitoring tool it executes 4 times.
I am wondering is it because SSIS is doing it in parallel execution automatically?
We use all default settings, and the server physical cpu is 4.
Also it says the query is slow is because it has a wait called PREEMPTIVE_OS_WAITFORSINGLEOBJECT
not sure what does that mean
View 0 Replies
View Related
Nov 6, 2015
what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.
MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.
I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :
0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?
View 4 Replies
View Related
Mar 18, 2015
I create a main program which will launch two jobs at a time, each job does some processes and at the end I'm trying to delete those jobs after storing the job details in one of the custom table I created (cleanup sub-program).
Out of two jobs I am able to store one job details (like job_name,job_id,start_time and end_time of the job) in the custom table and able to delete that job, but the job that's getting completed at the end is not getting captured nor getting deleted from sysjobs and sysjobhistory tables.
I had included this step (which will call the cleanup sub-program to store the job details and delete it) at the end. I can see that this cleanup procedure getting called from debug message but it is neither storing details nor deleting the job.
When I execute this cleanup program separately, it does store the job details and delete it.
View 0 Replies
View Related
Nov 30, 2006
I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:
I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.
In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.
In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get___ methods work as they had been in the previous reads.
It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?
View 8 Replies
View Related
Jan 22, 2015
I have a stored procedure that calls several views that rely on each other. In the past these views used to go parallel and use up all 100% of the CPU (12 cores), and now when the same stored procedure runs it only uses 8% of the CPU (1 core). This extends the time spent on the query from roughly 10-15 sec to 2-3min. I'm not quite sure why this is happening.
Are there some obvious things to look at when optimizing views to utilize all cores/threads? Also, it doesn't matter if I set Cost Threshold for Parallelism to 1 or 50 or 5, it is always the same, and I have Max Degree of Parallelism set to 0 as well, which should mean to use all cores when available.
View 9 Replies
View Related
Jun 1, 2015
I come from other ETL tools (Oracle Warehouse Builder, BODI, BODS & DataStage) and i'm having trouble finding the best practice for scheduling a collection of packages to be processed parallel en retry those that fail. I created a staging project which contains all the packages (50) that extract data from 1 source system and grouped the packages into 2 sequence containers to make sure that the 'heavy' packages are started first and together in parallel.
I soon discovered that there is no standard option to have one child package retry on failure. Currently if 1 package fails the whole project is retried.
I explored checkpoints as a solution but that seems a dead end when running packages in parallel.
There seem to be 2 solutions for my issue:
(1) create a loop around every EPT with 3 variables (waittime, retry_counter & succes_flag)
(2) create an event handler to keep a list of ID's that failed and enable/disable EPT's based on that list (there's a lot more to it).
Option 1 seems like a lot of bloatware in what i expected to be standard functionality. I'm still investigating option 2.
How do others handle this kind of scheduling? Is it so different with SSIS that i'm approaching this incorrectly ?
View 0 Replies
View Related
Aug 5, 2015
I have a table with 8 columns, I need to update data in multiple columns on this table, this table contains 1 million records, having single update was taking time so I broke the single update into multiple update statements and running multiple update statements in parallel, Each update statement updates different column.
This approach is working fine but I am getting the deadlock error.
Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried with various lock hints but no success.
View 4 Replies
View Related
Dec 11, 2014
I have a scenario where i have to run update task on multiple servers in parallel and once all of them are completed (success or failure) another task is to be run on another server
1. in maintenance plan, if we add tasks which are not joined, will they run in paralled at the same time
2. if we link the last task to all the tasks with link type 'completed' will the last task complete after all tasks are completed or when any one of them is completed (i have big doubt here)
the business requirement behind this is to bring data from multiple servers into shadow copies locally and then process them together. its ok if some server data transfer fails, but its not ok to start processing centrally while data transfer is going on. further, we want to run data transfer from multiple servers in paralleled to save time.
View 0 Replies
View Related
Oct 22, 2015
I have a deadlock trace running on a production server that is filling up with the following:
Lock: Deadlock Chain
Parallel query worker thread was involved in a deadlock SPID 8
105 - Resource Type Exchange
Spid 8 is a SQL process LOCK MONITOR
There are no graphs as seen with typical deadlock, so it is not two queries that are deadlocking, and I've never seen a deadlock for "LOCK MONITOR"
View 0 Replies
View Related
Apr 29, 2014
Any way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them. Also any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,
c.FirstName + '' '' + c.LastName as Name,
cus.CustomerNumber Code,
c.companyname as "Company Name",
a.Address1,
a.Address2,
[code]....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect.the server is using an old cached query plan, but don’t know for sure.
View 9 Replies
View Related
Feb 25, 2015
On one server I'm having an issue with and it having such a small procedure cache.
Server has 60GB of RAM assigned to its min and max server memory settings, optimise for ad hoc workloads is disabled.
Procedure cache at the moment on the server is 2.41MB with only 6 objects in side all related to mssqlsystemresource database, I can see stuff dropping in for user databases, but as soon as the proc has finished the plan is removed from the cache.
Buffer cache is in the 17GB mark, free pages around the 42GB mark so around 60GB used with a bit in stolen pages, but no proc cache.
All other servers in the environment are reporting over 8GB of proc cache in use which is more healthy.
Using Spotlight to monitor all of this.
Whats wrong with this one server and it not keeping the plans in cache.
View 9 Replies
View Related
Mar 27, 2012
I am trying to create a calculated member for parallel period function using ssas 2012. I have 10 measures for which i need to create parallelperiod.
I can successfully create for 1 measure but when i add multiple values to it it breaks. Below is the sample i tried for multiple measures:
sum(ParallelPeriod([Date].[Calendar].[year],1,[Date].[Calendar].currentmember) ,
([Measures].[Revenue],[Measures].[Expenses]))
View 10 Replies
View Related
Apr 30, 2014
way to invalidate cached query plans? I would rather target a specific query instead of invalidating all of them.
Also do you know of any sql server setting that will cause cached query plans to invalidate even though only one character in the queries has changed?
exec sp_executesql N'select
cast(5 as int) as DisplaySequence,
mt.Description + '' '' + ct.Description as Source,
[Code].....
In this query we have seen (on some databases) simply changing ‘@CustomerId int',@CustomerId=1065’ too ‘@customerId int',@customerId=1065’ fixed the a speed problem….just changed the case on the Customer bind parameter. On other servers this has no effect. I’m thinking the server is using an old cached query plan, but don’t know for sure.
View 3 Replies
View Related
Jul 11, 2014
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
View 7 Replies
View Related
Oct 29, 2001
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.
View 1 Replies
View Related
Feb 24, 2015
We have some Deadlock alerts set up in SQL Agent that email us when the performance counter for deadlocks goes above zero. I've used the following script to identify the event file which has deadlock information in there.
select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health'
Now that is fine, and we're looking into that (number of deadlocks appear to be 0.5) but out of interest ran a SQL Profiler session to capture the details as well and nothing is showing, I've received a few alerts and the trace file has information in there - but profiler shows absolutely nothing (all deadlock events are captured)
View 1 Replies
View Related
Oct 21, 2015
Is there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.
Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.
View 1 Replies
View Related
Aug 3, 2014
I am attempting to create a new trace but I get the following error message: "failed to start a new trace".
I have been doing some digging and as I understand it, I had to find the directory Profiler uses for temporary files. So, I typed the following in the command window "SET TMP" and I received the following reply:
C:UsersRossAppDataLocalTemp
Now, according to the forum: [URL] ...
I am supposed to check that the system folder pointed to by the TMP environment variable exists and is not crammed with files.
Well, when I went to the directory C:UsersRossAppDataLocalTemp, it is indeed full of both files and directories. The size is 16.3 MB and has 133 files and 63 folders.
When I had a look at the Environment Variables window and chose TMP the value is "%USERPROFILE%AppDataLocalTemp" which according to my limited understanding is the equivalent to C:UsersRossAppDataLocalTemp.
So, what I am wondering is am I supposed to totally clear out this directory? I am not too keen on doing this because I don't want to stuff my PC up.
View 3 Replies
View Related
Jan 27, 2015
For Bulk Load requests in SQL server, Are there any specific profiler event? Like the one we have for RPC RPC:Starting and for Batch Requests, we have SQL:BatchStarting.
Are Bulk Load requests that are being monitored through Profiler captured as SQL:Batch... events at the backend?
Are there any new features added in 2012 or 2014 to identify a Bulk request submitted through bcp.exe utility or any other sqlbulkcopy program?
View 1 Replies
View Related
Oct 2, 2014
We would like to benchmark our logical reads daily to show our improvement as we tune the queries over time.
I am using sys.dm_exec_query_stats summing the Physical and Logical Reads. Is this a viable option for gathering this metric? Is this a viable metric to gather?
select sum(total_physical_reads) as TotalPhyReads, sum(total_logical_reads) as TotalLogReads from sys.dm_exec_query_stats;
How best to provide performance based metrics.
View 4 Replies
View Related
Jul 12, 2007
Hi Experts,
We have a VLDB ( few table with above 200 million records ). This database is used for performance testing by simulating for 150 users and executing all necessary functional flows.
When I examined the profiler results , I could see some very high values as shown below in the duration column for many events.
1521729
3462142
1624325
3211255
1248276
3903998
Does it mean that that SP or the T-Sql statement is taking this much time in milliseconds to execute and give the output ?
Any help would be greatly appreciated.
Thanks & Regards,
DBLearner
View 1 Replies
View Related
Aug 22, 2014
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
View 9 Replies
View Related
Jan 30, 2015
which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.
View 1 Replies
View Related
Jul 27, 2006
I run Profiler against my production Db to keep an eye on poorly performing t-SQL batches and SPs. There's an "Error" column in the profiler output. According to BOL (topic:Describing Events by Using Data Columns), Error = The error number of a given event. Often this is the error number stored in sysmessages.
The profiler output usually shows "0 - OK" in the error column. But it sometimes shows "1 - Error" or "2 - Abort" in this column. What do these error messages mean? I've looked around but haven't been able to find anything more detailed about what these two messages mean and how I can perhaps dig deeper to figure out what's going on. (When these values are displayed, CPU is usually taking a beating and the SPID runs this for at least 30 seconds before displaying information pertaining to this SPID).
Thanks much,
Smitha
View 9 Replies
View Related
Dec 9, 2013
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
View 5 Replies
View Related
