Code Archives - Page 2 of 2 - Jon Shaulis

Do Synonyms impact performance in SQL Server?

I tried an experiment recently to test whether synonyms impacted performance in SQL Server.

Here’s our setup for the experiment:

-- Experiment table
CREATE TABLE test 
(one INT NOT NULL, 
two INT NOT NULL, 
three INT NOT NULL, 
four INT NOT NULL);

-- Get records
INSERT INTO test
VALUES (1, 1, 1) 
GO 10000

-- Make synonym
CREATE SYNONYM test2
FOR test;

-- Clear cache
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test
SELECT * from test2;
SELECT * FROM test;

When experimenting, I ran each SELECT independently and ran DBCC FREEPROCCACHE (DON’T DO THAT IN PRODUCTION) in between run times.

My first experiment, I created the table as a HEAP. I saw no change in plan or performance.

-- TEST 1.1 Synonym used in HEAP
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

Test 1.1: Synonym used in HEAP plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 117 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 1.2 Synonym unused in HEAP
-- Test base table
SELECT * FROM test;

Test 1.2: No synonym used in HEAP plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 131 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

The second experiment I added a primary key and clustered index, I also saw no change in plan or performance. Here’s the setup and first test.

DROP TABLE test;

CREATE TABLE test 
(one INT NOT NULL identity(1, 1) PRIMARY KEY, 
two INT NOT NULL, 
three INT NOT NULL, 
four INT NOT NULL);

INSERT INTO test(1,1,1)
GO 10000

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- TEST 2.1 Synonym used in B-Tree
-- Test synonym
SELECT * from test2;

Test 2.1: Synonym used in clustered table plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 16 ms, elapsed time = 102 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 2.2 Synonym unused in B-Tree
DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test base table
SELECT * FROM test;

Test 2.2: No synonym used in clustered table plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 83 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

For the last experiment I created a non-clustered index on the two column to include columns three and four as well and forced the use of an index.

CREATE NONCLUSTERED INDEX testnonc ON test (two);

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- TEST 3.1 Synonym used with B-Tree and Index
SELECT * FROM test2 WITH (INDEX(testnonc));

Test 3.1: Synonym used in clustered table with non-clustered index plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 104 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

-- TEST 3.2 Synonym unused in B-Tree with index
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test WITH(INDEX(testnonc))

Test 3.2: No synonym used in clustered table with non-clustered index plan

(10000 row(s) affected) Table ‘test’. Scan count 1, logical reads 20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 31 ms, elapsed time = 126 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

Ok, so there was actually no difference. So what’s going on? The short answer is that there is a phase that happens before SQL creates a Query Plan, the bind phase. So this means we won’t see the impact Synonyms have on the query. This also means that any if at all negative performance associated to the Synonym, is so negligible that it is not relevant to tuning or worth worrying about.

Scott Hodgin on Stack Exchange has compiled a few sources talking about the bind phase and how it has little to no impact.

Skill showcase – Pet store OLTP database

I was involved awhile ago in a non-officially-competitive but competitive all the same project, where I needed to create a database and showcase my knowledge set. The requirements were straight forward, vague, and yet complex. The timeline was one day, so I had to be smart about my time and use the proper toolset.

Here’s the assignment summarized:

Create a pet shop database that involves inventory/point of sale. I plan to sell pets, toys, supplies, and food. Please create a DB definition that will allow me to track purchases, inventory, vendors, holiday sales, and taxes.

It must work with high volume pet shop in the state and be compatible to expand to the rest of the states later.

So what did we end up with?

1. A project plan.
2. Load test documentation.
3. ERD of database.
4. Complete database documentation.
5. T-SQL script to create it all.

I think the end result from a single day timeline was great, even if that restriction made things more… “ruff”! And with that said, I’m proud to present… PetsDumb! Not quite as successful as PetSmart. Sorry for the bad jokes, I’ll stop “kitten” around now. Here’s the source files on my GitHub showing what I was able to accomplish with that description and time frame. Happy Saturday everyone!

https://github.com/Jonathanshaulis/Pet-Shop-OLTP-Database

Parameter validation failed in SSRS

Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError)

I saw this error the other day and it took me a minute to recall how I’ve fixed this in the past.

If you are unfamiliar with SSRS and Visual Studios, it can be a little “unfriendly” with some features. Specifically in this case, taking a parameter that used to be hidden and making it a visual integral part of the report. In my particular circumstance, this report is not testable in visual studio and has to be uploaded to a server in order to validate.

I found a few articles and posts, but none of them helped me in this circumstance:

Microsoft MSDN

Social MSDN Microsoft Forum

Forums ASP

SQL Server Central Forums

The things I tried I really thought that would work:

Changing one of my dependent parameters populated from a data set to refresh when it sees fit and not automatically every time.
Copying an existing and working parameter that is similar over the old one in the XML and tweaking it slightly.
Saving and exiting the report.
Deleting the parameter and re-creating the parameter.
Scouring the XML for all references to the parameter and making sure it all was kosher.

What did work in the end:

Deleting the report off the report server and re-uploading it. Overwriting the report / uploading and applying the updates to it did not change anything and it gave me a consistent error. After I deleted it from the server and re-uploaded it, the error was gone completely.

I have added in a form for feedback, it would be very helpful if you would take a few minutes to fill this out. My goal is to adjust the content to best help others resolve their problems.

Where did I put that thing in my SSIS project?

You may inherit a large SSIS project or create / use a large SSIS project someday, or perhaps there’s just a lot of packages. Either way, there’s going to come a day and time where you have to find something in your SSIS packages.

Say you need to remove a column in a table but you’re not sure where that column is referenced in your SSIS packages? Perhaps you are having metadata issues and you need to find every reference to that column? Or maybe you want to see what packages reference what tables, etc.

There are numerous ways to slice and dice this, I created essentially a powershell grep function to cursor through my dtsx packages and find strings referenced.

Here it is!

# Variables you should alter to your environment
$DirectoryToTarget="C:\Git\SSIS\PackageSets1\"
$WordToFind="TableSourceOne"
$PSGrepLog="C:\LogOutputs\PSGrepLog.txt"

# You will probably leave this variable alone. Set to search SSIS. 
$FilterType="*.dtsx"

Clear-Content $PSGrepLog

Get-ChildItem -Path $DirectoryToTarget -Filter $FilterType -Recurse | where { !$_.PSIsContainer } | % { 
    $file = Get-Content $_.FullName
    $containsWord = $file | %{$_ -match $WordToFind}
    If($containsWord -contains $true)
    {
        Add-Content $PSGrepLog $_.FullName
    }
}

Few notes here. It is not perfect, it is not case sensitive, and it will cursor child directories. It can take a few minutes to run depending on your system and the size of the directory. I would not recommend running this on a production server. You should copy the files you need to a nice location away from production and run this.

Once you have a list of packages with references, you can edit each package with a notepad editor or visual studio will let you examine the XML form. From here you can do a CTRL+F function on your keyboard and type in your searching keyword. It will take you to each instance / reference in your package, making it substantially quicker to find those rogue references.

Happy developing!

Edit on 5/4/18.

I have found out that visual studios actually offers the ability to perform the same functionality at a project level. I think my code is still helpful for particular situations, but you should be able to use the menu and find feature to search through all other packages too for your reference. I’m definitely saving my code as it should work across file types with a few minor tweaks.

Boolean order of precedence in T-SQL

I’ve been seeing a lot people get confused with their where clauses lately. Someone wanted to return where (X = A) and (Y = B or Z = C), but what they get is a totally different result. Sure it’s physically written and viewed as (X = A and Y = B or Z = C), but logically it holds a different meaning.

This demo will help illustrate this for you.

Let’s create our table variable, put a little data inside it, and see what it looks like afterwards.

DECLARE @TestTable TABLE 
(
id int primary key
,color varchar(10)
,number int
,animal varchar(25)
);

INSERT INTO @TestTable 
    (id,color, number, animal)
VALUES  
    (1,'blue', 1, 'monkey'),
    (2,'red', 1, 'monkey'),
    (3,'pink',1,'rhino'),
    (4,'purple', 2, 'monkey'),
    (5,'orange', 2, 'chicken'),
    (6,'black',2,'cat');

select * from @testtable;

Neat! We got data! Well, let’s try the above scenario.

This was my example earlier: (X = A and Y = B or Z = C)

Translated to where clause language: (number = 1 and color = ‘blue’ or animal = ‘monkey’)

This will translate completely to:

SELECT *
FROM @testtable
WHERE number = 1 AND
color = 'blue' OR animal = 'monkey'

Now here’s the result set once we run the query:

But hold up! We are getting three results back, we only expected to get back records 1 and 2. How did record 4 get in there? The answer is order of precedence. To make it perform logically how you wrote it out physically, you have two options.

The way many people would recommend to help protect against user error or accidents is to use parenthesis. These make it declaritive to at least show your logical intent behind the code.

SELECT *
FROM @testtable
WHERE (number = 1)
      AND (
      color = 'blue' OR animal = 'monkey')

You can re-write the query correctly though to forgo the need of parenthesis.

SELECT *
FROM @testtable
WHERE color = 'blue' OR animal = 'monkey'
AND number=1

If you run the query with the parenthesis fix or the logical fix, your results will be as seen below:

Regardless of your style or method, your team should have a standard for how they want the SQL styled and formatted. When your code becomes convoluted with unnecessary additions, it may be time to take a step back and evaluate how the code is written and how it can be tuned. You want it to be maintainable, short, and following best practices / ansi standards. The code should be documented, whether implied or explicitly, but of course preferrably both.

I do tend to personally use parenthesis even unnecessarily (like the example above) because I want that code to be implicitly documented. I want the next developer that sees this to know that this is the way I expect the logic to be followed. It can also lend itself to having less likely of a chance of personal error when writing the code.

So when in doubt, check your order of precedence but use your parenthesis with proper discretion and vigor!

Here is the handy reference for the order of precedence in T-SQL.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql

Share this:

Share this:

Share this:

Share this:

Share this: