The article states:

By some reports, between a half and three-quarters of carriers in Vò, were asymptomatic.

https://www.newsweek.com/coronavirus-mass-testing-experiment-italian-town-covid-19-outbreak-1493183

So what does this mean?  While I am by no means an Epidemiologist, it most likely means the virus is way more contagious that we think it is, and that many more people “have it” that previously believed, however these people exhibit no symptoms whatsoever, marking them as “asymptomatic carriers”.  How long the virus stays in an asymptomatic carrier is anyone’s guess, but I would venture to say no more than the 21 days as reported being the largest outlier.

We can correlate these asymptomatic findings with another article written by Israeli nobel laureate Michael Levitt.  The most convincing he cites is that of the Diamond princess.  He equates the diamond princess where he states:

The Diamond Princess cruise ship represented the worst-case scenario in terms of disease spread, as the close confines of the ship offered optimal conditions for the virus to be passed among those aboard. The population density aboard the ship was the equivalent of trying to cram the whole Israeli population into an area 30 kilometers square. In addition, the ship had a central air conditioning and heating system, and communal dining rooms.

“Those are extremely comfortable conditions for the virus and still, only 20% were infected. It is a lot, but pretty similar to the infection rate of the common flu,” Levitt said. Based on those figures, his conclusion was that most people are simply naturally immune.

He’s basically saying the same thing that the Northern Italy town of Vò may be finding.  That many people may have natural immunity to the disease and it passes quickly and unnoticed due to their being asymptomatic.  Of the entire 3,711 people on board, 712 tested positive for the virus.  The question is, does that mean the other 2,999 were exposed however have naturally immunity, or they had symptoms that passed very quickly?  If so, it may mean the virus is much more contagious that previously thought, and that everyone on the Diamond Cruise ship was exposed, but only 19.2% showed symptoms.  Of those 19.2% (712) 7 of them passed away representing just under 1%.  Keep in mind however they had exceptional medical care due to Japan’s medical system not being overrun like that of Italy’s.

source: https://www.jpost.com/HEALTH-SCIENCE/Israeli-nobel-laureate-Coronavirus-spread-is-slowing-621145

What does all this mean?  Like I said, I am no Epidemiologist, so take it with a grain of salt.  But if this is more contagious that previously thought, and 20% of those who do contract it show symptoms, that means potentially 80% are asymptomatic.  Now of the 20% that do show symptoms, approximately 20% of them need medical care in terms ventilators or respirators, resulting in about a 1% death rate of symptomatic cases.  This should not be taken lightly, as this is heartbreaking and will severely affect us, our families, and our society as a whole.  That is the bad, the very bad.  But the potential bright light of that, is if it is so contagious, the hope is this thing will run it’s course quickly.  Let’s just pray we can stave it off long enough to ensure we do not overwhelm the heroes that work in hospitals, putting their lives on the line to save us all.

>>What did your first signs feel like? Did you cough first and get fever later?

“First signs were being supppper achy and having a fever, cough came probably a couple days in but the chest pain/hard time breathing was there from the jump”

>> What was your temperature in the beginning?
102-103 the first time I got it checked

[deleted by user]
by inAMA

SECOND PERSON:

>> I keep getting messages about symptoms, so here’s a day by day.

Friday 6 day 1 : contact with the person for 45 mins in a small non ventilated room, along with my husband and son.

Mon 9 D4 : chills, low fever for me and toddler, feeling strange and an itchy throat some cough

Tues 10 D5 : diarrhea, strong throat ache and headaches. Low fever, Dry cough. This is a day we got the email Saying we were in contact

Wed 11 D6: dry cough, headaches, weight in my chest- like pressure. I’m pretty exhausted. No fever but I thought I was a lot better.

Thurs 12 D7 : bouts of dry cough, just like being so tired I can’t catch my breath. No pain but my chest feels tight.

Fri 13 D8: I coughed up a ‘spray’ of red blood, got freaked out. No phone lines to answer but I got through to a doctor who’s a friend and they said it looked like my throat was irritated. I was told to take my leftover meds from when I had bronchitis. Still hard to breathe, but the meds make it manageable. Headaches

Sat 14 D9 headache, not much cough, still tight chest… but a lot better

[deleted by user]
by inAMA

THREE:

When did I realise I maybe have covid-19? Short of breath

>>Whats the normal symptoms? (for you)

“Started with a cold like symptom minus the runny nose, short of breath and then I developed a cough.”

Yesterday I tested positive for Covid 19 . AMA
byu/fukDiarmo inAMA

FOUR:

>> Is it okay if you can list your symptoms? I’m just curious

“First 3 to 5 days: Fever Headache Caughing Weakness cardiovascula proplems

Day 5-13 Same as before Stomach pain Fainting when standing up Pain in the lungs but no breathing proplems

Day 13- 18 Headache Caughing Stomach Pain Really low fever

I think thats it”

I had the Corona Virus and spent 18 days in quarantine. I’ve now beaten it. AMA
byu/zwante- inAMA

FIVE:
Day 1: No symptoms, just anxiety if I got enough stuff. Later that night I ventured into cvs to pick up wat was left of cough medicine and painkillers. Thank god I did.

Day 2: Felt tired… slept a lot. Managed to maybe walk outside for some air. It was hard to take deep breaths as I usually do when I go for walks. Took some painkillers that night.

Day 3: Shit hits fan, as I realize this is no ordinary weekend hibernation. I felt my body drifting in and out of sleep. I knew it was coronavirus cuz I haven’t gotten sick in years.

Day 4: No other symptoms other than weakness and body giving out (can’t even stand in warm shower for more than 5 mins). General feeling of malaise overall. After the shower I went to doctor, they refused to test me cuz I didn’t exhibit enough symptoms. Went home hoping to just wait it out, but things didn’t get better.

Day 5: Walked into hospital telling nurses white lie that ppl at my work had coronavirus and I strongly believe I caught it from them. They took it seriously and wheeled me in for testing.. test came back and I was positive.

I have coronavirus… young and healthy, AMA
by inAMA

SIX:

https://www.foxnews.com/entertainment/frozen-2-rachel-matthews-positive-coronavirus

The actress said the first day she exhibited symptoms began with a “sore throat, fatigue and headache,” which progressed to a “mild fever” of 100.3 on day two. Her fever was accompanied by “horrible body aches, shortness of breath, major fatigue” and “no appetite” as well as a “deep, dry cough.”

SEVEN:

Game of thrones star:

I only have mild symptoms of a cold.

https://www.instagram.com/p/B9zvkEHBRjf/?utm_source=ig_embed
EIGHT:

>>What were some of your symptoms?

“It was like a headache, brain fog, feeling like dizzy and weird. Sore throat, cough. I was really cold. No matter how hot it was anywhere I was really cold. My nose was going between stuffy and runny.”
https://www.wmfe.org/orlando-tsa-worker-who-tested-positive-for-coronavirus-talks-symptoms-testing-and-whether-the-agency-is-doing-enough/148604

With that said, I will talk about the optimizer. It’s getting smarter and smarter at dealing with the majority of queries and keeping them within an even keel baseline. But that comes at a cost. It can’t take as many chances so it plays it safe. And this is where you come in. But before you go around adding hints everywhere in your system (which is a horrible idea). You need to know how to write code so that it will not NEED hints. To do this is very simple. The answer is KEEP IT SIMPLE. In other words, keep the queries simple. Do not create huge SQL Statements. It’s much better to break the queries up so that you do not give the optimizer many different routes to take. Breaking them up typically entails materializing the data into temp tables (write as little data as possible) so you can break up large queries into multiple small queries.

With that said, let’s get onto the hints. I’m not going to go over all of them, just the ones I use the most.

LOOP JOIN

This hint works in two ways. Often times when you are joining two tables, one table will be much smaller and IF the optimizer chose to join using this table first, you would have a very fast query. The inner loop join, changes the join order forcing these two tables to FIRST limit the result set by joining on each other first, AND it does so using a SEEK. The important thing to note about this technique, is that both of the join columns need to be indexed. If you try a loop join on two non indexed columns you will be in trouble.

To do an inner loop join, do the following:

[cc lang=”sql”]
SELECT *
FROM table_a a
INNER LOOP JOIN table_b b ON b.id = a.id;
[/cc]

Test the outcome before you do it. I usually only do this as an emergency measure, or a way to squeeze another couple hundred ms out of a highly called proc. I have never seen INNER LOOP JOIN error out. The optimizer has always been able to create an execution plan.

INDEX HINT

This actually is probably the most common. For whatever reason, the optimizer decides to use the wrong index. Most of the time it’s because stats changed or possibly a parameter sniffing issue on the last compile. This may be fine a lot of the time, but if it’s a critical process, it won’t fly, and you don’t want to be woken up at 2am.

[cc lang=”sql”]
SELECT *
FROM table_a a WITH (INDEX(ix_someindex))
WHERE a.id > 50;
[/cc]

INNER HASH JOIN

This join is the most efficient if you do not have indexes on the join columns. It saves time because not only does the optimizer not have to consider which type of join to use, but since you know the join columns have no indexes (or they are small tables) there is no reason to do a loop join. A hash join operates by create buckets of each side of the join columns, matching the buckets, then sorting each bucket to join the columns. That’s at least how I understand it. I have not seen the optimizer fail by using a hash join hint.

[cc lang=”sql”]
SELECT *
FROM table_a a
INNER HASH JOIN table_b b ON b.id = a.id;
[/cc]

INNER MERGE JOIN

This is a hint I never touch. The reason is because the optimizer CAN error out when trying to create a plan when this hint is applied. This is because each column must be sorted before the join. However, a merge join is the most efficient in both CPU and IO. A really cool trick I learned from an Adam Machanic post is below (he advocated not to do this as well). In theory this probably would not error out, but I would still not suggest doing it in production. However this would join things VERY quickly in a lot of cases. Again don’t do this

[cc lang=”sql”]
SELECT *
FROM table_a a
INNER MERGE JOIN
(
SELECT TOP (1000000000) — really large unreasonable number
FROM table_b b
ORDER BY b.id
) b ON b.id = a.id;
[/cc]

MAXDOP

This is one of the most common ones I use. The reason is because sometimes there will be a process that is invoked from the website, that really does not benefit from parallelism, or the parallelism ends up eating a bunch of CPU. Or on the flip side, a process that consumes a lot of data, needs a bit of a boost. A setting of 1, turns parallelism off. A setting higher than that, basically assigns the same number of spids to the number you assign.

[cc lang=”sql”]
— turn parallelism off
SELECT *
FROM table_a a
INNER JOIN table_b b ON b.id = a.id
OPTION (MAXDOP 1);

— turn it on to use 4 processors
SELECT *
FROM table_a a
INNER JOIN table_b b ON b.id = a.id
OPTION (MAXDOP 4);
[/cc]

FORCEORDER

I do not advocate using this one. What it’s supposed to do, never really works in my opinion. Forceorder should process the order of events from top to bottom. Utilizing the driver table (first table you specify) as being the lead table to limit the result set. For me, this simply does not work. The last time it worked for me was in SQL 2000. A better way to force the order of your query, is to force the order of events using virtual tables.

[cc lang=”sql”]
— this will in most cases, force the processing of table_a first
SELECT *
FROM (
SELECT a.id
FROM
(
SELECT a.id
FROM table_a
WHERE a.id = (1,4)
) t1
) t2
INNER JOIN table_b b ON b.id = t2.id;
[/cc]

OPTION (RECOMPILE)

This is another very common one. It usually gets placed on the big fat query you can’t touch (or don’t want to break up). It gets a bad hop on an execution plan one too many times and you say fock it, I’m going to throw this on so the bad hops don’t happen. Hey.. I’m just being real. But having to use this one too many times typically means your queries are overly complicated. You can probably benefit from breaking them up.

[cc lang=”sql”]
SELECT *
FROM table_a a
INNER JOIN table_b b ON b.id = a.id
INNER JOIN fat_table_c ON c.id = b.id
INNER JOIN fatter_table_d ON d.id = c.id
INNER JOIN fat_who_created_this_e e ON e.id = d.id
OPTION (RECOMPILE);
[/cc]
That’s all folks.

First Make sure it’s SQL Server

Sometimes there can be other processes (even backups) that are causing the CPU to push over that final edge. If you don’t have access to log into the box, the quickest way to determine how much CPU SQL Server is using is to run the following DMV:

[cc lang=”sql”]
DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info;

SELECT
record_id,
dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization
FROM (
SELECT
record.value(‘(./Record/@id)[1]’, ‘int’) as record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization,
TIMESTAMP
FROM (
SELECT timestamp, CONVERT(xml, record) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE ‘%%’) as x
) as y
ORDER BY record_id DESC;
[/cc]

If the OtherProcessUtilization column has a high value then you know you need to log into the box, open task manager and figure out what is hosing your system. It should be noted that you really should only run SQL Server on SQL Boxes, and if this is your issue, you’ll learn that today.

If it is SQL Server, then it typically falls into one of three categories.

First Category

You have a procedure that has caught a bad execution plan hop and it either needs to be recompiled, or have a hint applied. To figure this out, you need to run sp_who3. Compile this procedure into your master database and run it. If after running it you see multiple spids (rows) with the same ObjectName or SQLStatement, then you have a bad plan. Consider recompiling the procedure using sp_recompile:

[cc lang=”sql”]
exec sp_recompile ‘pr_procedure_name’;
[/cc]

If that does not work, you may want to consider flushing the entire procedure cache. Take note of the procedure name before you do this, as it may not show up in subsequent sp_who3 executions in case you need to find it again. You will need ALTER SERVER STATE permissions to perform this:

[cc lang=”sql”]
DBCC FREEPROCCACHE
[/cc]

If that does not work, you may need to open the procedure and make a modification. Unfortunately that a big fish we won’t be able to fry in this article. Your options could range from creating a new index for the bad query, forcing an index for the bad query, or applying a force order hint (like loop join).

Second Category

If the above is not the issue, you may have a single process that has gone rogue. This is more of an issue if you don’t have many CPU cores. The more CPU cores you have, the less this will likely impact you UNLESS you have your Server instance’s max degree of parallelism setting set too high, along with Query threshold for parallelism. See this article to learn how to configure that. However, it you do have a single process that has gone rogue, use the same process above to recompile it, or kill it if necessary. To kill it you need to run sp_who3 to find out what the SPID is, and use the kill command to kill it. Do not kill a spid if it is 50 or lower. These are internal SPIDs used by SQL Server.

[cc lang=”sql”]
KILL 555
[/cc]

Third Category

You have a lot of missing indexes you have not taken care of, but viola, you now need to add them. This is usually the case in most SQL Server instances that do not receive a lot of love. Developers keep pushing code and all is well until TODAY. Now you need to add indexes. If this is the case, you will simply see a lot of SPIDs (rows) show up when you run sp_who3. Your best bet (if you should choose) is to deploy the top 3 missing indexes into your system to see if that has an effect. And if your system does not get a lot of love, it very likely will. As one DBA asked me after doing this for a client, “Did we just unplug all the webservers?” (Because CPU went down by like 60%). I linked to it already, but you can find the DMV script which will display the missing indexes here. Just copy the index, name it properly, and deploy it. You should add the WITH (ONLINE=ON) if you are running SQL Server Enterprise Edition in production.
[cc lang=”sql”]
CREATE INDEX ix_someindex ON tablename (column1,column2) INCLUDE (momo1, momo2) WITH (ONLINE=ON);
[/cc]
That’s it, hopefully that helped you with your issue.

The common need to convert an INT to a string is to then concatenate it with either another int or an existing string.

Here is a simple example:
[cc lang=”sql”]
SELECT CAST(12345 AS VARCHAR(11))
[/cc]
And the output:

Here we are casting the int to a varchar(11). This is a safe value for us to convert to because the maximum integer value is -2147483648.

We see if we try to convert an integer to a smaller string, it returns back an asterisks (*) meaning an error has occurred.
[cc lang=”sql”]
SELECT CAST(-2147483648 AS VARCHAR(10))
[/cc]

So it is important to choose a data type that is large enough.

With that said, I have seen the following work as well, however I personally would not run this in production in case it is deprecated in a future version of SQL.

[cc lang=”sql”]
SELECT CAST(-2147483648 AS VARCHAR)
[/cc]

Here are the steps broken out in detail.

Right-click on the database that contains the table and choose tasks -> Export Data…

Click next past the intro screen and on the “Choose a Data Source Screen” make sure the database that contains your table is listed in the drop down. Then Select Next.

On the “Choose a Destination” Window, select “Flat File Destination” in the top drop down box labeled “Destination”.

On the same “Choose a Destination” Window, click the “Browse” button and the Open file dialog box appears prompting you to find a location for your file. Find the location, then type in the file name. THEN to the right of the file name text box, change the drop down from “Text Files (*.txt)” to “CSV Files (*.csv)” As shown below.

While still on the same “Choose a Destination” window, select whether you want the column names to be included in the CSV by selecting the “Column names in first data row” check box. Then Select Next.

On the “Specify Table Copy or Query” window make sure “Copy data from one or more tables or views” is selected then click Next.

On the “Configure Flat File Destination” window, select your source table or view. Leave the Row delimiter and column delimiter as the default then click Next.

The “Save and run package” window now appears. Make sure “Run immediately” is selected then click Next.

Finally the “Complete the wizard” window appears. The details with show “Copy rows from [your table] to [your csv location]. If this is correct, click “Finish”.

The execution actions will now be displayed along with the status of the export. If it finishes successfully, you will see the following screen.

That’s it! If your execution was not successful, check to see what the error messages say. Make sure you have access to the file location where you want to write the CSV file to. Good luck!

Simple snippet

[cc lang=”sql”]
SELECT sm.*, so.*
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK)
ON sm.[object_id] = so.[object_id]
WHERE sm.definition LIKE ‘%table_name%’
[/cc]

However if you’re going to be using this search a lot, and I can’t imagine you’re not, then why not encapsulate it in a stored procedure so you can include multiple terms, exclude terms, and sort. You will have to place this procedure in each database you want to search however.

Stored Procedure: sp_search

[cc lang=”sql”]
USE master
GO

CREATE PROCEDURE spsearch
@include varchar(max) = NULL,
@sort varchar(5) = 1, — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’
@exclude varchar(max) = NULL

AS
— EXEC sp_search ‘UPDATE,products,status’, 3
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Delimiter varchar(max) = ‘,’

IF OBJECT_ID(N’tempdb..#split’) IS NOT NULL DROP TABLE #split

;WITH split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@delimiter,@include) AS endpos
UNION ALL
SELECT CAST(endpos+1 as int), CHARINDEX(@delimiter,@include,endpos+1)
FROM split
WHERE endpos > 0
)
SELECT ‘Id’ = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
‘Data’ = ‘%’ + SUBSTRING(@include,stpos,COALESCE(NULLIF(endpos,0),LEN(@include)+1)-stpos) + ‘%’
INTO #split
FROM split;

IF ((SELECT MAX(id) FROM #split) > 3)
BEGIN
SELECT ‘cannot specify more than 3 include criteria’
RETURN
END

;WITH split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@delimiter,@exclude) AS endpos
UNION ALL
SELECT CAST(endpos+1 as int), CHARINDEX(@delimiter,@exclude,endpos+1)
FROM split
WHERE endpos > 0
)
SELECT ‘Id’ = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
‘Data’ = ‘%’ + SUBSTRING(@exclude,stpos,COALESCE(NULLIF(endpos,0),LEN(@exclude)+1)-stpos) + ‘%’
INTO #split_exclude
FROM split
WHERE @exclude IS NOT NULL;

IF ((SELECT MAX(id) FROM #split_exclude) > 3)
BEGIN
SELECT ‘cannot specify more than 3 exclude criteria’
RETURN
END

SELECT
so.name,
so.type_desc,
so.create_date,
so.modify_date,
sm.[definition]
FROM sys.sql_modules sm WITH (NOLOCK)
INNER JOIN sys.objects so WITH (NOLOCK)
ON so.[object_id] = sm.[object_id]
WHERE
(
(
NOT EXISTS (SELECT data FROM #split WHERE id = 1)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 1)
)
AND
(
NOT EXISTS (SELECT data FROM #split WHERE id = 2)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 2)
)
AND
(
NOT EXISTS (SELECT data FROM #split WHERE id = 3)
OR
sm.definition LIKE (SELECT data FROM #split WHERE id = 3)
)
)
AND
(
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 1)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 1)
)
AND
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 2)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 2)
)
AND
(
NOT EXISTS (SELECT data FROM #split_exclude WHERE id = 3)
OR
sm.definition NOT LIKE (SELECT data FROM #split_exclude WHERE id = 3)
)
)
ORDER BY
CASE WHEN @sort = ‘1’ THEN so.name END ASC,
CASE WHEN @sort = ‘2’ THEN so.name END DESC,
CASE WHEN @sort = ‘3’ THEN so.modify_date END DESC,
CASE WHEN @sort = ‘3’ THEN so.create_date END ASC

END
[/cc]

Example Usage

• first parameter is a comma delimited list of phrases you want to match. You do not need to add the wildcard % I did it for you in the code.
• second parameter is a sort option. — 1 =’name_asc’ 2 = ‘name_desc’ 3 = ‘date_desc’, 4 = ‘date_asc’
• third parameter is a comma delimited list of phrases you want to exclude

[cc lang=”sql”]
— look for the three words listed and sort base modify date desc:
EXEC sp_search ‘UPDATE,products,status’, 3
— look for the three words listed and sort base name asc, and exclude a single word:
EXEC sp_search ‘UPDATE,products,status’, 2, ‘customerid’
[/cc]

That’s it!

Like other software’s, we need to understand that MS SQL server is also a computer program but a complex program. Here Microsoft written this complex program. So if there is any problem with the SQL server, we need to understand why this program is not running as we expected.

From SQL Server we need to pull and push data as fast & accurate as possible. If we face any issues, reasons may be

• SQL Server (complex program) needs certain hardware and installation settings which we are not providing properly.
• The way SQL Server implemented and the way it understands T-SQL code, we are not providing proper T-SQL code to it

Even though MS SQL Server is a proprietary software, they provided us a lot of ways to understand the Server and what’s going on inside so that we can use it efficiently.

If the SQL server is running without errors, first we need to calculate wait statistics of different threads. SQL server uses threads for every user request. Again a thread is nothing but another program inside complex program which is called SQL server (This thread is not Operating system thread on which SQL server installed. This is related to SQLOS thread which is a pseudo operating system for the SQL Server).

We can find wait statistics using “sys.dm_os_wait_stats” DMV. There are many scripts online to query this view as per your needs. I like Paul Randal script(http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts)

[cc lang=”sql]

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] – [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N’BROKER_EVENTHANDLER’, N’BROKER_RECEIVE_WAITFOR’,
N’BROKER_TASK_STOP’, N’BROKER_TO_FLUSH’,
N’BROKER_TRANSMITTER’, N’CHECKPOINT_QUEUE’,
N’CHKPT’, N’CLR_AUTO_EVENT’,
N’CLR_MANUAL_EVENT’, N’CLR_SEMAPHORE’,
N’DBMIRROR_DBM_EVENT’, N’DBMIRROR_EVENTS_QUEUE’,
N’DBMIRROR_WORKER_QUEUE’, N’DBMIRRORING_CMD’,
N’DIRTY_PAGE_POLL’, N’DISPATCHER_QUEUE_SEMAPHORE’,
N’EXECSYNC’, N’FSAGENT’,
N’FT_IFTS_SCHEDULER_IDLE_WAIT’, N’FT_IFTSHC_MUTEX’,
N’HADR_CLUSAPI_CALL’, N’HADR_FILESTREAM_IOMGR_IOCOMPLETION’,
N’HADR_LOGCAPTURE_WAIT’, N’HADR_NOTIFICATION_DEQUEUE’,
N’HADR_TIMER_TASK’, N’HADR_WORK_QUEUE’,
N’KSOURCE_WAKEUP’, N’LAZYWRITER_SLEEP’,
N’LOGMGR_QUEUE’, N’ONDEMAND_TASK_QUEUE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,
N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
N’REQUEST_FOR_DEADLOCK_SEARCH’, N’RESOURCE_QUEUE’,
N’SERVER_IDLE_CHECK’, N’SLEEP_BPOOL_FLUSH’,
N’SLEEP_DBSTARTUP’, N’SLEEP_DCOMSTARTUP’,
N’SLEEP_MASTERDBREADY’, N’SLEEP_MASTERMDREADY’,
N’SLEEP_MASTERUPGRADED’, N’SLEEP_MSDBSTARTUP’,
N’SLEEP_SYSTEMTASK’, N’SLEEP_TASK’,
N’SLEEP_TEMPDBSTARTUP’, N’SNI_HTTP_ACCEPT’,
N’SP_SERVER_DIAGNOSTICS_SLEEP’, N’SQLTRACE_BUFFER_FLUSH’,
N’SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
N’SQLTRACE_WAIT_ENTRIES’, N’WAIT_FOR_RESULTS’,
N’WAITFOR’, N’WAITFOR_TASKSHUTDOWN’,
N’WAIT_XTP_HOST_WAIT’, N’WAIT_XTP_OFFLINE_CKPT_NEW_LOG’,
N’WAIT_XTP_CKPT_CLOSE’, N’XE_DISPATCHER_JOIN’,
N’XE_DISPATCHER_WAIT’, N’XE_TIMER_EVENT’)
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) – MAX ([W1].[Percentage]) < 95; — percentage threshold
GO

[/cc]

Please concentrate on top most rows first. They represent the maximum wait type. Also please read about different wait types on your top 10 rows online. You need to understand them perfectly so that you will take a good decision.

For Example:

If you have too much “PAGEIOLATCH_XX” that means a thread is waiting for data page reads from the disk into buffer (which is nothing but memory). We need to be very careful in understanding this scenario. It does not mean we have poor IO subsystem or less memory. You can solve this problem by increasing IO subsystem & memory temporarily. But to find out permanent solution we need see why we are reading so much data from the disk? What type of SQL commands are behind this? Are we unnecessarily reading too much data instead of reading less data using filters like where clause? Is too much data read happening because of table scans or index scans? Can we convert them to index seeks by implementing or modifying existing indexes? Are we writing SQL queries which are misunderstood by SQL optimizer (another program inside complex SQL server program)?

We need to think in different angles and use different test cases to come up with a final solution. Each of the above wait type needs a different solution. Any DBA need to research them thoroughly before taking any action. But most of the time 60 to 70% of the problems will be solved by finding problematic T-SQL queries and tuning them.

2) FINDING PROBLEMATIC QUERIES

You can use following query to find out top 20 worst performing queries:

[cc lang=”sql]

SELECT TOP 20
total_worker_time/execution_count AS Avg_CPU_Time
,Execution_count
,total_elapsed_time/execution_count as AVG_Run_Time
,total_elapsed_time
,(SELECT
SUBSTRING(text,statement_start_offset/2+1,statement_end_offset
) FROM sys.dm_exec_sql_text(sql_handle)
) AS Query_Text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC

[/cc]

What we need to observe here is even though a query can have maximum avg run time, but if it runs only once then the total effect on the server is low comparing to a query which has medium avg run time & runs lots of times in a day.

3) FINE TUNING QUERIES

Fine tuning T-SQL queries is a very big concept. Fundamental thing we need to understand is how well we can write T-SQL queries and implement indexes etc… So that SQL optimizer (a program inside MSSQL program) will find an optimized plan to do what we wanted it to do. Every new release of a SQL Server, we will get more sophisticated optimizer so that it will cover our mistakes in writing optimized SQL queries and also any bugs related to previous optimizer program. Like any human being how intelligent he may be, if we can’t communicate properly to him then he will misunderstand us. Same goes with computer programs.

MSSQL Server uses advanced search & sorting algorithms. If you are good at search and sorting algorithms then most of the time you can easily guess why SQL Server is taking particular action. The best book for understanding such algorithms is The Art of Computer Programming (Donald Knuth).

When we examine queries which are need to be fine-tuned, we need to use Execution plan of those queries so that we can find out how SQL server is interpreting them.

I can’t cover all the aspects of execution plan here but on a basic level I can explain the things we need to consider in execution plan.

• First we need to find out which are the operators taking most of the query cost
• If the operator is taking more cost then we need to see the reason for it. Most of the time scans will represent more cost than seeks. We need to examine why particular scan is happening (table scan or index scan) instead of index seek. We can solve this problem by implementing proper indexes on table columns but as with any complex computer program like SQL Server there is no fixed solution. For example if the table is small then scans are faster than seeks.
• There are approximately 78 operators which represent various actions and decisions of the SQL Server execution plan. We need to study them in-depth so that we can understand them better to take proper action.

Even if we implement proper indexes on tables & write good T-SQL code, if the execution plan is not reused then it creates performance problems.

4) EXECUTION PLAN RE-USE

After fine tuning the queries, we need to make sure that execution plan will be reused when necessary. Most of the CPU time will be spent on calculating execution plan which can be eliminated if we re-use the plan.

You can use below query to find out execution plan re-use:

[cc lang=”sql]

SELECT [ecp].[refcounts]
, [ecp].[usecounts]
, [ecp].[objtype]
, DB_NAME([est].[dbid]) AS [db_name]
, [est].[objectid]
, [est].[text] as [query_ext]
, [eqp].[query_plan]
FROM sys.dm_exec_cached_plans ecp
CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est
CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

[/cc]

Here “usecounts” represents how many times plan reused.

The best way to re- use execution plan is by implementing parameterized stored procedures. When we are not in a position to implement stored procedures we can use “sp_executesql”.

But as with any complex computer program there is no fixed solution. Sometimes it is better to compile the plan again.

For Example:

• select name from table where name = ‘sri’;
• select name from table where name = ‘pal’;

Let us assume we have non-clustered index on “name” column and half of the table has value ‘sri’ and few rows have “pal” in the “name” column. So for the 1st query SQL Server uses Table Scan because half of the table has same values. But for the 2nd query it is better to use Index Scan because only few rows have ‘pal’ value.

So even though queries are similar, same execution plan may not be good. Most of the time it may not be the case. So we need to carefully analyse everything before we decide whether it is better to use same execution plan or not.

If we don’t want to re-use execution plan we can always use “recompile” option in stored procedures.

Even after using stored procedures or “sp_executesql” there are times when execution plan won’t be re-used. They are

• When indexes used by the query changes or dropped
• When statistics, structure or schema of a table used by the query changes
• When we use “recompile” option
• When large number of insertions, updates or deletes happen
• When we mix DDL & DML within single query etc…

5) REMOVING UNNECESSARY INDEXES

After fine tuning the queries, we need to check how we are using indexes over all in SQL Server. Index maintenance requires lots of CPU & I/O. It is better to remove indexes if they are not used at all because every time we insert data into SQL Server it needs to update indexes also.

SQL server provides us “dm_db_index_usage_stats” DMV to find index statistics. You can use below query to find out index usage statistics:

[cc lang=”sql]

SELECT
OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME],
DB_NAME(IUS.database_id) AS [DATABASE NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = IUS.[OBJECT_ID]
AND I.INDEX_ID = IUS.INDEX_ID

[/cc]

6) MS SQL SERVER INSTALLATION & DATABASE SETUP

Please keep data and log files separately. Main reason for this is writing & accessing data files are not sequential, where as to log files it is sequential. So if we put them on the same drive then we can’t use them in an optimized way.

Please note that when you purchase SAN from vendor they may give you some recommendations on how to setup but it is not always useful. Please have a detailed discussion with your hardware & networking guys so that you can keep data and log files separately in an optimized way.

7) DON’T OVERLOAD SQL SERVER

The primary duty of any DBA is to make sure Production Server runs smoothly and serves the customers as best as possible. To make this happen we need to maintain separate databases (if possible separate machines) for the following environments:

• Production
• Development
• Testing
• Analytical

For Production we need a database with Full recovery mode & for other works Simple recovery mode is enough.

If we do testing on Production database then it will put lots of load on transaction log, indexes, CPU & I/O. That’s why we need to use separate databases for production, development, testing & analytical.

If possible please use separate machines for these activities because it will decrease load on CPU & I/O.

8) TRANSACTION LOG, TEMPDB & MEMORY

Please make sure your Log file has enough free space for normal operations because auto-grow operation on log file is time-consuming and it can force other operations to wait until it is completed.

You can use “DBCC SQLPERF(logspace)” to find out Log file size for each database and how much it is used.

The best way to setup tempdb is to put it on separate disk. Please keep initial size as max as you can afford because when it reaches auto grow situation performance decreases.

We need to make sure that SQL server runs on separate machine and there won’t be any other application on that machine. We need to keep some memory for operating system & some more if it is part of cluster etc… . So most cases keep around 2 GB for the OS and rest will be for the SQL Server.

Final conclusion: The above suggestions are for the basic performance tuning only. To do advanced performance tuning for the SQL Server, we need to dig deeper into each of the above areas.

Thanks
Sripal V
Database Performance Specialist
www.soulstech.com
email: sripal@soulstech.com

First let’s look at some DDL for 2 different tables:

[cc lang=”sql”]
CREATE TABLE [dbo].[ProcessLogMaster](
[process_log_master_id] [int] IDENTITY(1,1) CONSTRAINT PK_process_log_master PRIMARY KEY CLUSTERED NOT NULL,
[process_master_name] [varchar](100) NOT NULL,
[datetime_start] [datetime] NULL DEFAULT (getdate()),
[datetime_end] [datetime] NULL,
[elapsed_ms] [int] NULL,
[rows_updated] [int] NULL,
[rows_inserted] [int] NULL,
[rows_deleted] [int] NULL,
[complete] [tinyint] NULL DEFAULT ((0)),
[success] [tinyint] NULL,
[error_description] [varchar](max) NULL
)

CREATE TABLE [dbo].[ProcessLogDetail](
[process_log_detail_id] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_process_log_detail] PRIMARY KEY,
[process_log_master_id] [int] NOT NULL,
[process_detail_name] [varchar](100) NOT NULL,
[datetime_start] [datetime] NULL,
[datetime_end] [datetime] NULL,
[elapsed_ms] [int] NULL,
[rows_updated] [int] NULL,
[rows_inserted] [int] NULL,
[rows_deleted] [int] NULL,
[complete] [tinyint] NULL,
[success] [tinyint] NULL,
[error_description] [varchar](max) NULL
)
[/cc]

What we have here are two different tables that can be used to describe job steps. The ProcessLogMaster table, is used to specify the Master or Top level of the job. If there are multiple steps to the job, that report back to the master, we would enter their entries into the ProcessLogDetail table. We could then sum up the rows updated / inserted / deleted into the ProcessLogMaster table. You’ll also notice the ProcessLogDetail table contains the foreign key to the ProcessLogMaster table. We did not designate it as such here simply for performance reasons.

AN example would consist of creating an entry into these tables, often within your stored procedure, then update the corresponding id that relates to that portion of the job

[cc lang=”sql”]

CREATE PROCEDURE pr_example_proc_job
AS

/********************************************************************
* declarations
********************************************************************/
DECLARE @process_master_name varchar(100) = ‘refresh_all_clients’;
DECLARE @process_log_master_id int;
DECLARE @now datetime = GETDATE();
DECLARE @datetime_start datetime;
DECLARE @datetime_since datetime;

INSERT INTO ProcessLogMaster
(
process_master_name,
datetime_start
)
SELECT
@process_master_name,
getdate();

SET @process_log_master_id = SCOPE_IDENTITY();

BEGIN TRY
— this will create an entry in the processLogDetail table
EXEC [pr_example_detail_proc_1]
@process_log_master_id = @process_log_master_id;

— this will create an entry in the processLogDetail table
EXEC [pr_example_detail_proc_2]
@process_log_master_id = @process_log_master_id;

— gather detail logging
DECLARE @total_rows_updated int;
DECLARE @total_rows_inserted int;
DECLARE @total_rows_deleted int;

SELECT
@total_rows_updated = ISNULL(SUM(rows_updated),0),
@total_rows_inserted= ISNULL(SUM(rows_inserted),0),
@total_rows_deleted = ISNULL(SUM(rows_deleted),0)
FROM ProcessLogDetail
WHERE process_log_master_id = @process_log_master_id;

END TRY
BEGIN CATCH
— handle error
UPDATE ProcessLogMaster
SET datetime_end = GETDATE(),
elapsed_ms = DATEDIFF(ms, @now, GETDATE()),
rows_updated = @total_rows_updated,
rows_inserted = @total_rows_inserted,
rows_deleted = @total_rows_deleted,
complete = 1,
success = 0,
error_description = ERROR_MESSAGE()
WHERE process_log_master_id = @process_log_master_id;

RETURN;

END CATCH

UPDATE ProcessLogMaster
SET datetime_end = GETDATE(),
elapsed_ms = DATEDIFF(ms, @now, GETDATE()),
rows_updated = @total_rows_updated,
rows_inserted = @total_rows_inserted,
rows_deleted = @total_rows_deleted,
complete = 1,
success = 1
WHERE process_log_master_id = @process_log_master_id;

[/cc]

As you can see, we summarize the updates, inserts, and deletes and insert the total into the ProcessLogMaster table, from the ProcessLogDetail table. Also, if there is an error, the job never gets set to success = true. This being the case, we can always start our job back up from the last successful timestamp if we are doing a delta process.

I’ve found over the years these tables to be a necessary part of keeping track of jobs. Not only will it help you in viewing the duration of a job, but for doing delta processes, you can easily get the last time a job has succeeded and pick up from there.

There are times where you will need to use values other than the passed in parameters in order to create the cache key. Some examples include datetime data types or keys that are unique (like a customerkey). If the hash that gets created from the parameters is unique, then you will never reuse that dataset again. With this in mind you would even have determine whether the procedure is even cacheable. Another concern to keep in mind is the duration of time you can serve stale data. Maybe 30 seconds, 1 minute, or 1 hour? Any time increment is able to be worked with by clearning the cache tables at the desired interval.

Design

Let’s look at the basic workflow for how this procedure will work. First of all, we will need to hash all the parameters that are coming into the procedure (unless they are unique in which case we may not be able to cache, or we can possibly use another value).

Caching the parameters

[cc lang=”sql”]
— example parameters
DECLARE @cust_type INT = 5
DECLARE @cust_location int = 10
DECLARE @location_category int = 20
DECLARE @page_number int = 2
DECLARE @records_per_page int = 100

DECLARE @param_hash varbinary(20)

SET @param_hash = HASHBYTES(‘MD5’,
coalesce(convert(nvarchar(MAX),@cust_type),”) +
coalesce(convert(nvarchar(MAX),@cust_location),”) +
coalesce(convert(nvarchar(MAX),@location_category),”) +
coalesce(convert(nvarchar(MAX),@page_number),”) +
coalesce(convert(nvarchar(MAX),@records_per_page),”)
)

SELECT @param_hash
[/cc]

Now we have the cache key that will correspond to the recordset that we either will create, or will look to see exists.

Managing the cache

You may have thought this through enough already and realized if we store all the result sets for the procedure in one table, then when we delete or update based on whether the cache is expired, we’re going run into either contention, or an empty result set. This is where our race condition safe — caching solution comes into play. It involves using 3 tables instead of 1 or 2 in order to store the cache results. Why 3? Hold on, here comes my attempt at explaining.

If we use 1 table, then when we need to invalidate the cache, we would have to perform an update on all the records for each existant cache key as they come in. This will no doubt cause contention with blocking.

If we use 2 tables, then we can switch over to table 2 once the cache is expired for table 1, then trucate table 1. However, a subtle race condition comes into play in this scenario. When the procedure is executed and it is determined that table 1 is the valid table to use, unless you wrap the entire code in an explicit transaction, by the time it reaches the bottom of the procedure, the cache may have switched to table 2 as being the primary cache table, meaning table 1 has now been truncated. Guess what, you’re returning no data now.

So that is why we use 3 tables. We do not truncate table 1 when table 2 represents the current cache table. That way, when we reach the end of the procedure, we can still serve a result set because nothing has been truncated. Instead we truncate table 3, which is two tables back from being used (surely not being queried any longer). We then round robin through the tables in this manner.

Here’s an example of the cache invalidator job that would run at a set interval

[cc lang=”sql”]

USE cachedb
GO

DECLARE @cache_table_old int
DECLARE @transaction_error int
DECLARE @debug_mode int = 0

BEGIN TRANSACTION

— find the table we need to truncate
SET @cache_table_old =
(
SELECT CASE WHEN cache_table_current = 1 THEN cache_table_count ELSE cache_table_current – 1 END
FROM proc_mutex pm
WHERE pm.[procedure_name] = ‘pr_procedure_example’
);

— set the new cache table (the cached procedure pr_procedure_example will read this table to determine cache)
UPDATE TOP(1) pm
SET cache_table_current = CASE WHEN cache_table_current = cache_table_count THEN 1 ELSE cache_table_current + 1 END,
datetime_modified = GETDATE()
FROM proc_mutex pm
WHERE pm.[procedure_name] = ‘pr_procedure_example’

set @transaction_error = @@ERROR;

IF @transaction_error != 0
BEGIN
ROLLBACK
END
ELSE
BEGIN

COMMIT TRANSACTION
— truncate the old cache table
IF @transaction_error = 0
BEGIN

IF @cache_table_old = 1
BEGIN
TRUNCATE TABLE cachedb.tmp.pr_procedure_example_cachedb_1
END
ELSE IF @cache_table_old = 2
BEGIN
TRUNCATE TABLE cachedb.tmp.pr_procedure_example_cachedb_2
END
ELSE IF @cache_table_old = 3
BEGIN
TRUNCATE TABLE cachedb.tmp.pr_procedure_example_cachedb_3
END

END

END
[/cc]

And here is the ddl for the proc_mutex table. This table will hold all the procedure names that you cache, along with the current cache table designation.

[cc lang=”sql”]
CREATE TABLE [dbo].[proc_mutex](
[proc_mutex_id] [int] IDENTITY(1,1) NOT NULL,
[procedure_name] [varchar](255) NOT NULL,
[cache_table_current] [int] NOT NULL,
[cache_table_count] [int] NOT NULL,
[datetime_modified] [datetime] NOT NULL
)
[/cc]

It is recommended you keep the caching on a separate database which is stored on a PCIE SSD card, or at least an SSD

Creating the procedure

Now that we have created the cache invalidator mechanism, along with the proc_mutex, we will write the first half of the procedure. This will ideally be a template you can follow:

[cc lang=”sql”]
USE proddb
GO

CREATE PROCEDURE dbo.pr_procedure_example
@cust_type INT = 5,
@cust_location int = 10,
@location_category int = 20
AS
BEGIN

/**********************************************************
* setup param_hash_key
***********************************************************/
DECLARE @procedure_name sysname ;
DECLARE @cache_table_number tinyint ;

SET @procedure_name = OBJECT_NAME(@@PROCID);
SET @cache_table_number = (SELECT TOP(1) pm.cache_table_current FROM cachedb.dbo.proc_mutex pm with (nolock) WHERE pm.[procedure_name] = @procedure_name);

DECLARE @param_hash varbinary(20)

SET @param_hash = HASHBYTES(‘MD5’,
coalesce(convert(nvarchar(MAX),@cust_type),”) +
coalesce(convert(nvarchar(MAX),@cust_location),”) +
coalesce(convert(nvarchar(MAX),@location_category),”) +
coalesce(convert(nvarchar(MAX),@page_number),”) +
coalesce(convert(nvarchar(MAX),@records_per_page),”)
)

/**************************************************************
* determine which table may have our param_hash_key
* if the key does exist, we will simply return our result set
* this will result in a fast return < 5ms
***************************************************************/

IF @cache_table_number = 1
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_1
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_1
WHERE param_hash = @param_hash

RETURN

END
END
ELSE IF @cache_table_number = 2
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_2
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_2
WHERE param_hash = @param_hash

RETURN

END
END
ELSE IF @cache_table_number = 3
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_3
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_3
WHERE param_hash = @param_hash

RETURN

END
END
[/cc]

Above, we are looking up to see if the result set has already been created for that combination of parameters. If it does, we will return the result quite fast. Depending on the size of data and the disk speed it could be somewhere between 1ms to 10ms. Let’s take a look at the full example.

Full cached procedure example

[cc lang=”sql”]
USE proddb
GO

CREATE PROCEDURE dbo.pr_procedure_example
@cust_type INT = 5,
@cust_location int = 10,
@location_category int = 20
AS
BEGIN

/**********************************************************
* setup param_hash_key
***********************************************************/
DECLARE @procedure_name sysname ;
DECLARE @cache_table_number tinyint ;

SET @procedure_name = OBJECT_NAME(@@PROCID);
SET @cache_table_number = (SELECT TOP(1) pm.cache_table_current FROM cachedb.dbo.proc_mutex pm with (nolock) WHERE pm.[procedure_name] = @procedure_name);

DECLARE @param_hash varbinary(20)

SET @param_hash = HASHBYTES(‘MD5’,
coalesce(convert(nvarchar(MAX),@cust_type),”) +
coalesce(convert(nvarchar(MAX),@cust_location),”) +
coalesce(convert(nvarchar(MAX),@location_category),”)
)

/**************************************************************
* determine which table may have our param_hash_key
* if the key does exist, we will simply return our result set
* this will result in a fast return < 5ms
***************************************************************/

IF @cache_table_number = 1
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_1
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_1
WHERE param_hash = @param_hash

RETURN

END
END
ELSE IF @cache_table_number = 2
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_2
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_2
WHERE param_hash = @param_hash

RETURN

END
END
ELSE IF @cache_table_number = 3
BEGIN

IF EXISTS(
SELECT 1
FROM cachedb.tmp.pr_procedure_example_cachedb_3
WHERE param_hash = @param_hash
)
BEGIN

SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_3
WHERE param_hash = @param_hash

RETURN

END
END

/**************************************************************
* procedure logic goes here
***************************************************************/
SELECT *
INTO #holding_table
FROM proc_example_table
WHERE cust_type = @cust_type
AND cust_location = @cust_location
AND location_category = @location_category

/**************************************************************
* insert result set to cache table if not exists
***************************************************************/
IF @cache_table_number = 1
BEGIN

INSERT INTO cachedb.tmp.pr_procedure_example_cachedb_1
SELECT
param_hash = @param_hash,
*
FROM #holding_table
— prevent duplicate result sets
WHERE NOT EXISTS (SELECT 1 FROM cachedb.tmp.pr_procedure_example_cachedb_1 WHERE param_hash = @param_hash);

END
ELSE IF @cache_table_number = 2
BEGIN

INSERT INTO cachedb.tmp.pr_procedure_example_cachedb_2
SELECT
param_hash = @param_hash,
*
FROM #holding_table
WHERE NOT EXISTS (SELECT 1 FROM cachedb.tmp.pr_procedure_example_cachedb_2 WHERE param_hash = @param_hash);

END
ELSE IF @cache_table_number = 3
BEGIN

INSERT INTO cachedb.tmp.pr_procedure_example_cachedb_3
SELECT param_hash = @param_hash,
*
FROM #holding_table
WHERE NOT EXISTS (SELECT 1 FROM cachedb.tmp.pr_procedure_example_cachedb_3 WHERE param_hash = @param_hash);

END

/**************************************************************
* return back result set
***************************************************************/
IF @cache_table_number = 1
BEGIN
SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_1
WHERE param_hash = @param_hash
END
ELSE IF @cache_table_number = 2
BEGIN
SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_2
WHERE param_hash = @param_hash
END
ELSE IF @cache_table_number = 3
BEGIN
SELECT *
FROM cachedb.tmp.pr_procedure_example_cachedb_3
WHERE param_hash = @param_hash
END

END

[/cc]

There it is. Needless to say, the respective tables (cachedb.tmp.pr_procedure_example_cachedb_1) need to have the same schema as the procedure that’s being returned. And again, put these tables on a PCIE SSD, and setup the job to invalidate the cache at a set interval, and there you have it, a procedure cache mechanism to help you scale.