December 18, 2016 at 8:36 pm
Hi Good Day,
Kindly please help me on my problem regarding the reporting services of SQL server.
I have a table which stores all the data that we need and used for reporting, it has a 50 columns and has null values, whenever we do a reporting(20-40 users) which spikes the CPU usage up to 80%, some reports failed to query the results. We have seen that some of the reports first select data, then updates an ID for the report(ex. rptyyymmddhhmmss) and selects the said ID as the report number.
We think that there is a deadlock issue so we used this settings for our database.
ALTER DATABASE <db_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON;
It didn't solve the issue and there are still users who cannot perform a report for printing.
We also checked the Activity Monitor > Processes there is a wait type of ASYNC_NETWORK_IO which was used on the SELECT query statements. There are 13 ASYNC_NETWORK_IO that are active on the process.
Should we kill those processes that makes the query of the reporting slow?
Or should we leave it like that and focus more on the query for the reports?
Or was it on our network that it takes too much time to query the reports that sets the other users to timeout on their transaction?
We are currently using SQL Server 2008 Enterprise
December 18, 2016 at 10:08 pm
aokol (12/18/2016)
Or should we leave it like that and focus more on the query for the reports?
Performance, or a lack of it, is in the code. I'd concentrate on fixing the code and making sure the appropriate indexes are both available and being used. Spiking CPU to 80% for reports is going to be the tip of the iceberg if you don't. You're already starting to see deadlocks. All of that can be fixed by making the code perform better. And remember... "Set Based" <> "All in one Query. "Divide'n'Conquer".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2016 at 11:11 pm
Hi Jeff,
Thank you for the reply, we checked the tables and it has indexes on primary key and foreign keys(also keys that will be used on other searching). The query is only selecting columns needed for the report on a table view. But since the program was created by a 3rd party, we check on how the query on the report works since on the table view that was selected has no where clause.
Kindly see the Enabling data pre-filtering in SQL-based reports on this link
https://technet.microsoft.com/en-us/library/dn531099.aspx
When using an alias like CRMAF_filteredtableviewname on a table view, it changes the table query inside as a selection of all columns. Since this is a 3rd party software, there is nothing that we can do on the filtered alias since this is where it gets the id needed to be reported.
The reporting has 3 data set queries, the first query is getting the data using the pre-filtering, the 2nd query is generating the id of the report, and the 3rd query is updating the report id on the table view, then select it on the table.
I still don't know if there are still settings that we need to do, since all I know is simple SQL, and I am now struggling on fixing this problem.
December 19, 2016 at 8:02 am
There are a bunch of common problems you have to avoid in SQL Server to aid in performance. Generally, check these first. They include, but aren't limited to:
functions on columns in filter functions (WHERE, ON, HAVING clauses)
multi-statement table valued user defined functions
nesting views or functions
bad or missing statistics
inappropriate or missing indexes
table variables where statistics are needed
Look through your code for those issues. Then, you have to look to the execution plans to understand what's happening.
In short, you're stepping into the big, beautiful, frustrating world of query tuning.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2016 at 6:26 pm
Hi Grant,
Thank you for the reply.
functions on columns in filter functions (WHERE, ON, HAVING clauses)
As for this one, it has only 1 column where clause that gets the main id of the data.
multi-statement table valued user defined functions
There are so many functions that was included on the table view that was created, we are still investigating if we can remove some of them if they are not needed on the table view. But since this one was a system generated view, we are hesitating to do this one since it can affect the process of the table selection.
nesting views or functions
As we checked the query of the table view, there are nested views that was used, we think that this one was created by the 3rd party group.
inappropriate or missing indexes
Since indexes on the primary key, foreign key and other columns used for searching was already used, was it not enough for the indexes? Or should we try to re-index everything?
The reporting has 3 data set queries, the first query is getting the data using the pre-filtering, the 2nd query is generating the id of the report, and the 3rd query is updating the report id on the table view, then select it on the table.
Update for this one, we checked the update query, and it updates the id through the table view that was in the first query statement, we've also tried to change it to update the base table, still there are timeouts on the reporting when using the select statement. We also think that the problem lies on the update query since it locks the tables that are used on the reports. Is there another way to reduce the locking when updating the rows on the table?
December 20, 2016 at 4:28 am
Without seeing the actual queries and how you're doing the processing, all I can really offer is vague support. It does sound like you may have some standard code smells going on there, but I'm just guessing based on what you said. Concentrate on the code smells and then the execution plan. That should lead you in the right direction. For loads more detail, see the books in my signature line below.
If you post the code, you're more likely to get specific advice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 20, 2016 at 6:32 pm
Hi Grant,
Thank you for your reply again, sorry but I can't give the actual query of the 3 dataset, I can only provide a sample. The report was made from SQL Server Report Builder so it uses xml for the design and queries
Dataset1 - this is where we get the information for the report.
FilteredOrders - table view
SELECT job_order_num, job_type, job_item, serials, customer_name, person_name FROM FilteredOrders AS CRMAF_FilteredOrders INNER JOIN FilteredPerson ON FilteredPerson.person_id = CRMAF_FilteredOrders.person_id
Note: there is no where clause in the first query, when you use CRMAF_tablename alias it changes the main table query(FilteredOrders). Kindly see the Enabling data pre-filtering in SQL-based reports on the link for more information.
https://technet.microsoft.com/en-us/library/dn531099.aspx
Sample of using pre-filtering on the Dataset1 FilteredOrders(with an alias of CRMAF_FilteredOrders)
SELECT FilteredOrders.* FROM FilteredOrders WHERE job_Id = @param
The param was get on the screen after clicking the report button, the changing of the alias was done by the program so we cannot do anything about it, unless we can change the program that the 3rd party used, but since this is already an installed program, there is nothing we can change inside the code, only the query for the report.
Dataset2 - where we select the id needed for the report
rpt_maintenance - table view
counter - how many times it was generated
name - the name of the report(ex. rpt, inv,sls)
date - date today
SELECT counter, name, date FROM rpt_maintenance WHERE name = 'rpt'
Output: RPT201612211
Dataset3 - update and select the report id
job_view - table view(before we change it to the base table)
UPDATE job_view SET job_rpt_id = @paramrptid WHERE job_id = @paramjobid
SELECT job_rpt_id FROM job_view WHERE job_id = @paramjobid
December 20, 2016 at 8:52 pm
Since you can't provide us with the queries to even try to help, then there's only one option. Instead of going through all of this yourself, get a hold of the 3rdd party provider, provide them with actual execution plans and things like SQL Profiler runs that show the resource usage, and hold their bloody feet to the fire.
Either that or find a better piece of software.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2016 at 10:18 pm
Hi Jeff,
Thanks for the reply.
The sample query that I've given was identical to the queries used on the 3 dataset, I just changed the tables and column names. Since the cause of the locking as what we have seen is during the update, we can say that there is a problem on how this thing was developed.
I think I really should give up on tuning this since it took longer than a day to fix the problem, and give it back to the 3rd party who made this.
Anyways thank you for your time answering my question.
December 21, 2016 at 4:26 am
I agree with Jeff (which is pretty standard). It's almost impossible to provide assistance here without details. Sorry.
If you want to continue to poke at it, then I'd suggest capturing the execution plans for each of the queries. Since all of them now have filter criteria (and they sure should), you can see how that criteria is being resolved inside the execution plan. You want to look for things like scans instead of index seeks (assuming very small result sets, which you seem to indicate). You want to look for key or bookmark lookups. These suggest the possibility of an index that could be added to the table that might improve performance.
However, this is where it gets fun, adding indexes also adds overhead, in terms of storage required, and additional processing to maintain the index. You have to be very cautious about adding new indexes to tables. You just have to have measurements and testing set up to ensure you don't negatively affect the system.
You're moving into a complex area. You really might want to bring in outside consulting as Jeff suggested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply