SQL 2012 :: How To Trace For Parallel Queries
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
ADVERTISEMENT
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 17, 2006
Hi,
Is it possible to execute queries in parallel on multiple linked servers? I retrieve the checksum of a table on a linked servers like this:
SELECT * FROM OPENQUERY(Server1, 'SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*) FROM Table1')
I need to do this on multiple linked servers at the same time - is this possible? I tried the following but my workstation executes the queries sequentially:
SELECT ("Query Server1"), ("Query Server2"), ("Query Server3"),..
Any suggestions??????
Rgds
Bob
View 1 Replies
View Related
May 1, 2000
I am optimising a application and need to find the queries which are taking lot of time to execute and which are scanning through many records (probably because indexing not proper).
I think that the tool to do this is SQL Trace. I created a trace but don't know how to interpret the data or what to look for.
Can someone please share their experience as to what info/data can one look at to find these "bad" queries.
Thanks!
- Manoj
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
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
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
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
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
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 22, 2014
I have this script running to list all FK relationship but it's only for single db,tried to make it work for inter DBs
DECLARE @Tname varchar(40) = 'CustAccount' -- <@>< Table Name to trace
-------------------------------------------------
SELECT DISTINCT No ,
ist1.table_catalog AS [Parent DB],
ist1.table_schema AS ParentSchema,
ParentTable ,
ReferencedColumnName ,
ConstraintName ,
link,
ForeignTable,
ForeignKeyColumn ,
[Code] ....
View 2 Replies
View Related
Jul 30, 2014
I am in the middle of capturing a workload to try and tune a SQL instance and was wondering what kinds of sizes people capture in terms of traces. I am only 1 day into a capture and I believe a typical workload would be a week long capture and I am already at 10GB of files. I am only capturing rpc_completed and sql_batch_completed.
What sizes of workloads do other people capture and then where do you analyse them, do you have particular dedicated server for this kind of thing as at present I am looking to use my local PC. Also what rollover file sizes do people tend to use, I am currently using 1GB.
View 7 Replies
View Related
Sep 2, 2014
Where would i find my particular database Error log file, Event log file and Trace file etc?
View 5 Replies
View Related
Mar 21, 2014
Somehow someone turned on a audit on the sql server and it is filling up our hard drive and shutting down sql server eventually. Been trying to google how to shut this audit off but coming up with no via soolution yet. how can I turn this trace off. Each fiel says AuditTrace and date and they happen every other minute. I went into the sql profiler and can pull up the files but how to shut the trace off, it does not say.
View 9 Replies
View Related
Sep 10, 2014
I am getting deadlock in my production, i was taken deadlock information from trace file , i found deadlock graph but i am unable to find exact scenario . I am attaching deadlock trace file.
View 5 Replies
View Related
Jul 23, 2014
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
View 2 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
Sep 12, 2015
how to trace or monitor previous actions in sql server 2012. Like previous using delete command . I want to know who is used that command in server.
View 2 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
Apr 24, 2015
Is there a dbcc flag that will capture all error messages in the log
e.g. when inserting data into a table, a PK violation occurs throwing, Msg 2627
..similar to trace flag 1222 to capture deadlock info..
View 3 Replies
View Related
Oct 13, 2015
After carefully analyzing the situation for almost a month, I pulled the trigger and made an exception at work; we did enable trace flag 2371. We have some tables with billion of rows and outdated statistics causing horrible plans. Tried several methods to update those, but did not solve the problem or was too CPU intensive, causing other issues.
Anyway, one of the side effects I am seeing so far is average vCPU went down by almost 40%. Nothing out of usual (besides the flag) has been enabled or was executed. So my assumption is, CPU hungry plans are now gone or reduced.
View 2 Replies
View Related
Sep 5, 2013
I am upgrading my application's SQL Server from 2008 R2 to 2012.
As discussed in the below URL I am able to see the Identity jump after I upgrade and the server is restarted.
Now since I cannot afford this and at this moment I do not have the time to create a sequence with NOCACHE and test it again I have to go ahead and add trace flag 272 in the start up parameter as this is the only solution which I can implement and even rollback without much hassles.
[URL] ....
What I got to know, this flag will disable the new feature of IDENTITY property that has been implemented as part of SQL Server 2012 and will make it work like it was doing in SQL Server 2008 R2.
But I want to know implementing this flag would impact any other feature or performance (except the performance of IDENTITY) of SQL Server.
View 8 Replies
View Related
Mar 13, 2014
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
del $(ESCAPE_SQUOTE(SQLDIR)) + "LogTestTrace.trc"
View 4 Replies
View Related
Jun 15, 2015
After SQL server service restart, a column which is set to auto increment jumped 1000. To fix the issue, I had to add T272 trace flag to SQL startup parameters. However, I did not see the column being reseeded after the service restart, it is still showing the 1000 jump. Am I doing something wrong?
Below the log showing the flag being added to the error log:
LogDateProcessorInfoErrorMSG
2015-06-15 22:29:53.850ServerRegistry startup parameters:
-d E:DATAmaster.mdf
-e E:logERRORLOG
-l E:DATAmastlog.ldf
-T 272
View 3 Replies
View Related
Dec 18, 2014
I am wondering if it is possible to make 1 query that gives several, different resultsets.
I am working on a database for speedskating times, in particular I am working on seasonlists
The query I use for this i (in simplified form)
SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.
I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.
In the current setup I need to make 6 queries.
Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?
View 9 Replies
View Related
Nov 2, 2015
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
View 3 Replies
View Related
May 13, 2014
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies
View Related
Jul 10, 2014
We recently upgraded to sql server 2012. We have xxx-D-011 as OLTP server and yyy-D-011 as distributor server.
The log is showing deadlocks every day between application queries/updates and replication jobs.
A fragment of the log about the deadlock is included below.
2014-07-10 15:31:05.94 spid13s deadlock-list
2014-07-10 15:31:05.94 spid13s deadlock victim=process37ced3498
2014-07-10 15:31:05.94 spid13s process-list
2014-07-10 15:31:05.94 spid13s process id=process37ced3498 taskpriority=0 logused=0 waitresource=OBJECT: 8:532249001:0 waittime=357 ownerId=860304057 transactionname=SELECT lasttranstarted=2014-07-10T15:31:05.090
[code]....
View 9 Replies
View Related
Jul 14, 2014
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
View 3 Replies
View Related
Feb 27, 2014
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
[Code] .....
View 1 Replies
View Related
