July 28, 2017 at 1:27 am
Hi all,
I would explain my problem in detail.
We have a c# code that calls a sql query viz. a T.Sql query (embedded sql file in the solution)
This code accepts an input and is called for multiple inputs one after other.
This sql code creates temporary tables and temporary variables, with joining some physical tables and returns the output.
Basically we are not inserting / updating any physical table in this sql code, this sql code outputs several resultset back to c#
The problem is initially it runs fast for some inputs but in the end it starts taking longer time and eventually gets transaction timeout.(The call to this sql code is being made within transaction scope of .net)
I have seen people have used OPTION(Recompile) in almost 5 places while creating temp tables, for which I think is not required since we are creating temporary table and the input to this query is also a temporary table and not a parameter.
Your inputs are required and helpful
July 28, 2017 at 2:26 am
Can you post the code?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2017 at 2:33 am
Hi Gail,
Posting the code might not be possible.
However, when I am running the same code for any one input, the code is taking usual time and nothing looks like causing a timeout.
What could be the reason for slowness of return, when the Tsql code is executed 'N' number of times for different input
Let me know if you need to verify something in the query or if you are looking at possibility of something.
July 28, 2017 at 8:09 am
er.mayankshukla - Friday, July 28, 2017 2:33 AMHi Gail,
Posting the code might not be possible.
However, when I am running the same code for any one input, the code is taking usual time and nothing looks like causing a timeout.
What could be the reason for slowness of return, when the Tsql code is executed 'N' number of times for different input
Let me know if you need to verify something in the query or if you are looking at possibility of something.
If you run something N times then it is expected that the duration will be N times as long as a single run.
If you run code with different inputs (tabular or parameter) you can get VASTLY different performance due to either a) large row count differences, b) data value skew or c) widely varying input parameters. OPTION (RECOMPILE) can help with those, but cannot solve all problems.
If you cannot post the code, query plans, table schemas (with indexing), etc. then you will need to hire a performance tuning professional that WILL be allowed to see those things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 3, 2017 at 9:31 pm
Yesterday, we ran a batch of 3000.
And enabled the profiler to capture the sql query, time taken, cpu, reads etc.
All 2999 inputs ran as expected, however the last 1 input took high time.
I ran the below query to see the waiting query:SELECT req.session_id ,blocking_session_id ,ses.host_name ,DB_NAME(req.database_id) AS DB_NAME ,ses.login_name ,req.status ,req.command ,req.start_time ,req.cpu_time ,req.total_elapsed_time / 1000.0 AS total_elapsed_time ,req.command ,req.wait_type ,sqltext.textFROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltextJOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_idWHERE req.wait_type IS NOT NULL
And I could see below result
| session_id | blocking_session_id | host_name | DB_NAME | login_name | status | command | start_time | cpu_time | total_elapsed_time | command2 | wait_type |
| 79 | 0 | WIN | Pr | user | suspended | SELECT | 13:49.7 | 9401 | 738.823 | SELECT | ASYNC_NETWORK_IO |
Does this Network IO wait could be a problem and if yes, what might be the reason for this ?
August 4, 2017 at 1:10 am
er.mayankshukla - Thursday, August 3, 2017 9:31 PM
Does this Network IO wait could be a problem and if yes, what might be the reason for this ?
NetworkIO is a well-documented wait.
As for a problem, no idea, how long was the session waiting for it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2017 at 1:11 am
er.mayankshukla - Friday, July 28, 2017 2:33 AMWhat could be the reason for slowness of return, when the Tsql code is executed 'N' number of times for different input
Higher load on the server due to you running it N times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2017 at 4:56 am
er.mayankshukla - Thursday, August 3, 2017 9:31 PMYesterday, we ran a batch of 3000.
And enabled the profiler to capture the sql query, time taken, cpu, reads etc.All 2999 inputs ran as expected, however the last 1 input took high time.
I ran the below query to see the waiting query:SELECT req.session_id ,blocking_session_id ,ses.host_name ,DB_NAME(req.database_id) AS DB_NAME ,ses.login_name ,req.status ,req.command ,req.start_time ,req.cpu_time ,req.total_elapsed_time / 1000.0 AS total_elapsed_time ,req.command ,req.wait_type ,sqltext.textFROM sys.dm_exec_requests reqCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltextJOIN sys.dm_exec_sessions ses ON ses.session_id = req.session_idWHERE req.wait_type IS NOT NULLAnd I could see below result
session_id blocking_session_id host_name DB_NAME login_name status command start_time cpu_time total_elapsed_time command2 wait_type 79 0 WIN Pr user suspended SELECT 13:49.7 9401 738.823 SELECT ASYNC_NETWORK_IO Does this Network IO wait could be a problem and if yes, what might be the reason for this ?
ASYNC_NETWORK_IO wait type occurs when the client application is unable to pick or catch up with the readying of the processed data at SQL server. It can be normal . In ur case I advise to look at the amount and type / range of data (skewed) which might be much larger compared to that generated for the other input parameters. Also check the elapsed time for this wait_type for the other inputs to have see difference.
Arshad
August 30, 2017 at 3:54 am
Again bringing out the same issue.
There are 2 ec2 instances with Sql server installed, one is prod and other is staging, they both have same machine configurations and same sql configurations.
Running a set of calculation, in Prod causes a last calculation to take much higher time and then finally timeout, however it runs well in time in the staging environment.
When I say set of calculation, A set of calculation will contain around 4k records for which multiple embedded sql queries will be run against the sql server.
There will be select, Insert, Delete and update statements.
In order to make sure I have the same dataset, I tested it after restoring the db from prod to staging, still the same result.i.e. fast in staging but failing in prod.
For reference below is the output of sp_configure.
| name | minimum | maximum | config_value | run_value |
| access check cache bucket count | 0 | 65536 | 0 | 0 |
| access check cache quota | 0 | 2147483647 | 0 | 0 |
| Ad Hoc Distributed Queries | 0 | 1 | 0 | 0 |
| affinity I/O mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity64 I/O mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity64 mask | -2147483648 | 2147483647 | 0 | 0 |
| Agent XPs | 0 | 1 | 1 | 1 |
| allow updates | 0 | 1 | 0 | 0 |
| backup compression default | 0 | 1 | 0 | 0 |
| blocked process threshold (s) | 0 | 86400 | 0 | 0 |
| c2 audit mode | 0 | 1 | 0 | 0 |
| clr enabled | 0 | 1 | 0 | 0 |
| common criteria compliance enabled | 0 | 1 | 0 | 0 |
| contained database authentication | 0 | 1 | 0 | 0 |
| cost threshold for parallelism | 0 | 32767 | 5 | 5 |
| cross db ownership chaining | 0 | 1 | 0 | 0 |
| cursor threshold | -1 | 2147483647 | -1 | -1 |
| Database Mail XPs | 0 | 1 | 0 | 0 |
| default full-text language | 0 | 2147483647 | 1033 | 1033 |
| default language | 0 | 9999 | 0 | 0 |
| default trace enabled | 0 | 1 | 1 | 1 |
| disallow results from triggers | 0 | 1 | 0 | 0 |
| EKM provider enabled | 0 | 1 | 0 | 0 |
| filestream access level | 0 | 2 | 0 | 0 |
| fill factor (%) | 0 | 100 | 0 | 0 |
| ft crawl bandwidth (max) | 0 | 32767 | 100 | 100 |
| ft crawl bandwidth (min) | 0 | 32767 | 0 | 0 |
| ft notify bandwidth (max) | 0 | 32767 | 100 | 100 |
| ft notify bandwidth (min) | 0 | 32767 | 0 | 0 |
| index create memory (KB) | 704 | 2147483647 | 0 | 0 |
| in-doubt xact resolution | 0 | 2 | 0 | 0 |
| lightweight pooling | 0 | 1 | 0 | 0 |
| locks | 5000 | 2147483647 | 0 | 0 |
| max degree of parallelism | 0 | 32767 | 0 | 0 |
| max full-text crawl range | 0 | 256 | 4 | 4 |
| max server memory (MB) | 128 | 2147483647 | 100000 | 100000 |
| max text repl size (B) | -1 | 2147483647 | 65536 | 65536 |
| max worker threads | 128 | 65535 | 0 | 0 |
| media retention | 0 | 365 | 0 | 0 |
| min memory per query (KB) | 512 | 2147483647 | 1024 | 1024 |
| min server memory (MB) | 0 | 2147483647 | 100000 | 100000 |
| nested triggers | 0 | 1 | 1 | 1 |
| network packet size (B) | 512 | 32767 | 4096 | 4096 |
| Ole Automation Procedures | 0 | 1 | 0 | 0 |
| open objects | 0 | 2147483647 | 0 | 0 |
| optimize for ad hoc workloads | 0 | 1 | 0 | 0 |
| PH timeout (s) | 1 | 3600 | 60 | 60 |
| precompute rank | 0 | 1 | 0 | 0 |
| priority boost | 0 | 1 | 0 | 0 |
| query governor cost limit | 0 | 2147483647 | 0 | 0 |
| query wait (s) | -1 | 2147483647 | -1 | -1 |
| recovery interval (min) | 0 | 32767 | 0 | 0 |
| remote access | 0 | 1 | 1 | 1 |
| remote admin connections | 0 | 1 | 0 | 0 |
| remote login timeout (s) | 0 | 2147483647 | 10 | 10 |
| remote proc trans | 0 | 1 | 0 | 0 |
| remote query timeout (s) | 0 | 2147483647 | 600 | 600 |
| Replication XPs | 0 | 1 | 0 | 0 |
| scan for startup procs | 0 | 1 | 0 | 0 |
| server trigger recursion | 0 | 1 | 1 | 1 |
| set working set size | 0 | 1 | 0 | 0 |
| show advanced options | 0 | 1 | 1 | 1 |
| SMO and DMO XPs | 0 | 1 | 1 | 1 |
| transform noise words | 0 | 1 | 0 | 0 |
| two digit year cutoff | 1753 | 9999 | 2049 | 2049 |
| user connections | 0 | 32767 | 0 | 0 |
| user options | 0 | 32767 | 64 | 64 |
| xp_cmdshell | 0 | 1 | 0 | 0 |
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply