Performance issues querying tables from datawarehouse

Transact-SQL
1

I'm querying tables from the datawarehouse that don't have a primary key and no index (at least I guess so and I don't know how to prove). I am not the admin of the DWH and I most likely won't be heard if I would ask that the cube's tables were build in a different way. So I have to live with the set-up that is there.

SQL Management Studio

  • My parameterized query takes 0 seconds to execute for one parameter returning 2000 records,
    but 3 minutes when executed with a different paramter that returns 200 record.
  • When removing the option (recompile), both querys are executed within a second

When calling these querys from inside a web application:

  • the query with the 2000 records is executed immediately
  • the query with the parameter that returns 200 records takes again some minutes and causes a timeout.
  • OPTION (recompile) does not have an impact on this behavior

Any explication of what is going on or suggestion of how to solve it?
Martin

2

I hardcoded the parameters in the query to eliminate the possible cause "parameter sniffing", but this doesn't change anything. The same query with:

... where COLUMN = 'A'

returns 2000 records, while

... where COLUMN = 'B'

returns 200 records. While both return instant results in SQL Management Studio, when called in the application the second query causes a timeout after more than a minute; the first reacts instantly.

Why are both queries treated so different?
Martin

3

Where is the server the app runs on in relation to the backend? Is it inline SQL query or stored procedure being called.
"Most likely won't be heard" what kind of place is this to work in? Who are the stakeholders of this app?

4

It's a .net web application with a sqlconnection to the SQL Server that is located on a different server than the web server where I design the application. The SQL query is inline.
In the meantime I learned about the instance configuration parameter ARITHABORT which might be responsible for that different behavior, as it is set to "ON" in SSMS while it's set to "OFF" for extern connections such as the .net SQLCONNECTION. It should be ON on both sides to grant the same execution plan. I will apply to the server admin to have this default setting changed; but I'm kind of afraid that they won't be too cooperative. I am working in a subsidiary of a bigger company and any change request is thoroughly and slowly questioned . :wink:

5

That is indeed one setting that could help help but make sure to also see if you can use stored procedures and also index on the columns used in the where clause.

6

You can change those settings on the connection in your web application - you don't need to change the default in SQL Server.

7

Be careful, you may not want the same plan.
For example, for:
where COLUMN1 = 'A'
vs.
where COLUMN1 = 'B'
vs.
where COLUMN1 = 'Z'
Any of these could need a different plan from any other one. It all depends on row counts and indexes available.

We don't have nearly enough details to figure out exactly what is going on. But, to properly tune this type of query, you will have to know what index(es) are on the table and review the actual query plan itself.

8

According to the popular article of Erland Sommarskog this is not fully correct, as ARITHABORT can only be set in the connection string, if the default value for ARITHABORT has been priorly set to "on" for exterbal connections like .net, ADO or ODBC for the server instance. Default value is "off" and this is not changed by SET ARITHABORT ON

9

The default at the server level will be overwritten by the connection. If you change the default at the server it may not have any impact because the connection is setting it. It would be much better to set it in your connection and be sure it is set the way you want it - instead of relying on a server default.

If you modified your code to use a stored procedure and set the values in the procedure - you would be sure to get the same settings regardless of which client is calling/executing the code.

10

I ran this code that is supposed to first change the ARITHABORT value to "ON" and then checks the status of ARITHABORT (taken from Erland's post). The query returns "OFF" in my case.

SET ARITHABORT ON
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int);
INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure 'user_options';
SELECT [setting] = 'ARITHABORT ' + CASE WHEN ([config_value] & 64) = 64 THEN 'ON' ELSE 'OFF' END
FROM @options;

I will try and see what changes when I place the code in a stored procedure tomorrow

11

What I learned now is that the execution plan can not be adapted according to the optimizer if ARITHABORT is set to off. I can reproduce this slow behavior showed when executing the query through a .net connaction if I set ARITHABORT to "OFF" in SSMA

12

You should insure that these settings are set as directed by MS (and your DBA, because not setting these correctly could cause execution errors if certain types of indexes are present):

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET XACT_ABORT ON;

13

I changed the primary Key to a composite primary Key and this nightmare is over. It cost me 3 days, I suffered and learned a lot, distroyed one keyboard and my cat hates me. I would love to have more time and learn more but I'm not paid for this

1 Like