September 24, 2015 at 7:35 am
robinwilson (9/23/2015)
READ_COMMITTED_SNAPSHOTThis option does look like a possible option and would seem better than the suggestion of using NO LOCK on everything. I have read up on this and can't really see a downside apart from possibly a higher level of activity in temp db. We do see a lot of locking in activity monitor.
Using this does add a 14-byte row version to each row as they are versioned. This can cause page splits in your indexes, causing increased index fragmentation. Still, this is usually a better choice over adding NOLOCK everywhere.
Since you are on 2014, you have the full range of the window functions available to remove the cursors. However, since this is commercial software, they probably:
1. Won't do it
2. Need to support < 2012, which only has partial support of the window functions.
I doubt that having you recode the commercial software to be efficient would be something that you'd be allowed to do.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 24, 2015 at 9:38 am
GilaMonster (9/24/2015)
robinwilson (9/23/2015)
Thanks all for the information and detailed responses which is all really useful.Heaps
I was told this by the software supplier:
I accept that clustered indexes do seem like a simple solution and with system tables, especially the extremely large audit table, we have resorted to implementing this approach, but wider usage will bloat the database size and can have detrimental impacts and so this has been avoided. Hopefully as is being shown efficient queries don’t need to rely on clustered indexes
Wahahahahahahahaha... *rotfl*
No, clustered indexes won't bloat the DB, a well-chosen clustered index doesn't have detrimental effects and that really sounds like someone trying to use fancy words to hide that they don't have a clue.
The SQL storage engine is implemented based on the assumption that all tables will have clustered indexes. It should be the norm in a SQL server DB, not the exception.
Code, Cursors and NO LOCK
We are being told that what we are trying to do is too complex for the system to handle.
Again, hahahahahahaha!
One client I have uses a single SQL instance and single sql DB to run an online accounting package with tens of thousands of users. I have multiple clients using SQL for stock market trading systems. Insurance processing systems, HR systems, online gambling, etc.
I doubt what you're doing is too complex for SQL Server.
Too complex for the vendor's design, now that's a possibility.
I have to add my :hehe::hehe::hehe::hehe: to this one.
This is a first rate vendor </sarcasm>
Yeah you are not going to get very far with this vendor.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 24, 2015 at 10:00 am
SQLRNNR (9/24/2015)
GilaMonster (9/24/2015)
robinwilson (9/23/2015)
Thanks all for the information and detailed responses which is all really useful.Heaps
I was told this by the software supplier:
I accept that clustered indexes do seem like a simple solution and with system tables, especially the extremely large audit table, we have resorted to implementing this approach, but wider usage will bloat the database size and can have detrimental impacts and so this has been avoided. Hopefully as is being shown efficient queries don’t need to rely on clustered indexes
Wahahahahahahahaha... *rotfl*
No, clustered indexes won't bloat the DB, a well-chosen clustered index doesn't have detrimental effects and that really sounds like someone trying to use fancy words to hide that they don't have a clue.
The SQL storage engine is implemented based on the assumption that all tables will have clustered indexes. It should be the norm in a SQL server DB, not the exception.
Code, Cursors and NO LOCK
We are being told that what we are trying to do is too complex for the system to handle.
Again, hahahahahahaha!
One client I have uses a single SQL instance and single sql DB to run an online accounting package with tens of thousands of users. I have multiple clients using SQL for stock market trading systems. Insurance processing systems, HR systems, online gambling, etc.
I doubt what you're doing is too complex for SQL Server.
Too complex for the vendor's design, now that's a possibility.
I have to add my :hehe::hehe::hehe::hehe: to this one.
This is a first rate vendor </sarcasm>
Yeah you are not going to get very far with this vendor.
+1
Tells you that some vendor's staff is more outdated than they're software.
😎
September 25, 2015 at 6:01 pm
Thanks for the interesting replies!
The vendor has now taken a copy of our database and sent me a query with a few table-value functions they could run within 47 seconds and which took 57 minutes when I ran it on the college system.
They are now saying it is because we need to split tempdb into four files. Whilst this may be best practice and may help somewhat I can't see it causing such a discrepancy but we will need to do it now they have recommended it. It's about all I have to go on.
In regards to read_committed_snapshop they said they couldn't comment but that their software hadn't been tested with it turned on.
They also have a really strange way of managing primary keys which they think could be an issue. They have a single table which holds a numeric value and is used to assign sequential numbers to the ID field (that should be the primary key!) across all tables when adding records. So every ID across the entire system is unique. It also made it a right pain to insert data into one of their tables. It looks like due to this design decision read_committed_snapshot could allow the same number to be retrieved by two simultaneous processes. Why couldn't they just have used identity columns or even a standard sequence? They do seem to have made some unusual design decisions.
Just to explain the "complexity" of what I am trying to do. I am trying to find a way to report live on learner data (about 5000 records) showing the highest grade they have achieved across a range of 10 core subjects. I use a table-valued function for each subject (after converting from scalar value functions from advice here) as it needs to rank them as they may have done the same subjects more than once). This works out as almost a second per learner. At my last college (with another system) I had no problem querying this data live. We struggle with the simplest things here.
I do have a test version of the same system and we have added primary keys to all the main tables but all the functions and stored procedures have the live instance name in them and I need to replace all occurrences of [ulive] with [utest]. Is there an easy way to do this or do I need to go through each one individually? I was thinking there was probably a way to update the system tables but don't want to mess it up.
Thanks
Robin
September 26, 2015 at 1:36 am
GilaMonster (9/24/2015)
Kristen-173977 (9/23/2015)
GilaMonster (9/23/2015)
Functions in the select execute once per row, and they have an overhead. Data-accessing scalar functions in the select are essentially a hidden cursor inside a select and they are horrific in terms of performance.Sorry, my mistake I was thinking of scalar functions which manipulate the parameters rather than data-accessing scalar functions.
Same thing, they still execute once per row, they still have an overhead. They are not in-line functions.
Sorry, missed your reply earlier.
So if I have a UDF to calculate Cube Volume, with parameters @Length, @Width, @Height is that any different (apart from some overhead for using the function, which I have assumed to be "tiny") to putting "LengthCol * WidthCol * HeightCol" in my SELECT?
Or perhaps my mistake is in using the term "scalar" in this regard?
(I'll take a tiny overhead in using the function for the benefit of centralising code which is frequently reused)
September 26, 2015 at 1:39 am
robinwilson (9/25/2015)
Just to explain the "complexity" of what I am trying to do. I am trying to find a way to report live on learner data (about 5000 records) showing the highest grade they have achieved across a range of 10 core subjects.
Do you need "todays data" in order to do this, or would "yesterdays data" be OK? If yesterday's would do (or you can freshen up an Enquiry Database on-demand) then might that be an option? (I'm assuming that suitable Clustered / Non-Clustered indexes would be added to the Enquiry database)
Given the instance name issue you might need a separate server? 🙁 or only use it for your self-rolled queries?
September 26, 2015 at 2:58 am
Kristen-173977 (9/26/2015)
GilaMonster (9/24/2015)
Kristen-173977 (9/23/2015)
GilaMonster (9/23/2015)
Functions in the select execute once per row, and they have an overhead. Data-accessing scalar functions in the select are essentially a hidden cursor inside a select and they are horrific in terms of performance.Sorry, my mistake I was thinking of scalar functions which manipulate the parameters rather than data-accessing scalar functions.
Same thing, they still execute once per row, they still have an overhead. They are not in-line functions.
Sorry, missed your reply earlier.
So if I have a UDF to calculate Cube Volume, with parameters @Length, @Width, @Height is that any different (apart from some overhead for using the function, which I have assumed to be "tiny") to putting "LengthCol * WidthCol * HeightCol" in my SELECT?
Or perhaps my mistake is in using the term "scalar" in this regard?
(I'll take a tiny overhead in using the function for the benefit of centralising code which is frequently reused)
Pitching in my 2 Cents, unfortunately there is quite a misconception still around UDFs and the actual performance penalty implied. As mentioned earlier on this thread, the scalar functions are executed once per row with an execution overhead similar to a stored procedure. This overhead quickly mounts up making the scalar functions many times slower than either an inline table value function or a calculation in the select statement.
😎
Quick demonstration
Test harness
USE TEEST;
GO
SET NOCOUNT ON;
--/* --UNCOMMENT THIS LINE TO SKIP RECREATING THE TEST HARNESS
--AND COMMENT OUT TO GENERATE IT
IF OBJECT_ID(N'dbo.SUDF_CALC_CUBE_VOLUME') IS NOT NULL DROP FUNCTION dbo.SUDF_CALC_CUBE_VOLUME;
IF OBJECT_ID(N'dbo.ITVFN_CALC_CUBE_VOLUME') IS NOT NULL DROP FUNCTION dbo.ITVFN_CALC_CUBE_VOLUME;
IF OBJECT_ID(N'dbo.TBL_CUBE_DIMENSION') IS NOT NULL DROP TABLE dbo.TBL_CUBE_DIMENSION;
CREATE TABLE dbo.TBL_CUBE_DIMENSION
(
CD_ID INT NOT NULL CONSTRAINT PK_DBO_TBL_CUBE_DIMENSION_CD_ID PRIMARY KEY CLUSTERED
,CD_LENGTH INT NOT NULL CONSTRAINT DFLT_DBO_TBL_CUBE_DIMENSION_CD_LENGTH DEFAULT ( 1 + (ABS(CHECKSUM(NEWID())) % 100))
,CD_HEIGHT INT NOT NULL CONSTRAINT DFLT_DBO_TBL_CUBE_DIMENSION_CD_HEIGHT DEFAULT ( 1 + (ABS(CHECKSUM(NEWID())) % 100))
,CD_WIDTH INT NOT NULL CONSTRAINT DFLT_DBO_TBL_CUBE_DIMENSION_CD_WIDTH DEFAULT ( 1 + (ABS(CHECKSUM(NEWID())) % 100))
);
GO
CREATE FUNCTION dbo.SUDF_CALC_CUBE_VOLUME
(
@LENGTH INT
,@HEIGHT INT
,@WIDTH INT
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN
(
SELECT @LENGTH * @HEIGHT * @WIDTH
)
END
GO
CREATE FUNCTION dbo.ITVFN_CALC_CUBE_VOLUME
(
@LENGTH INT
,@HEIGHT INT
,@WIDTH INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT @LENGTH * @HEIGHT * @WIDTH AS DIMENSION
GO
DECLARE @SAMPLE_SIZE INT = 1000000;
INSERT INTO dbo.TBL_CUBE_DIMENSION(CD_ID)
SELECT TOP(@SAMPLE_SIZE)
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
)
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2
CROSS JOIN sys.all_columns SAC3
CROSS JOIN sys.all_columns SAC4
;
GO
-- */
Test Execution
USE TEEST;
GO
SET NOCOUNT ON;
---- TEST EXECUTION ----
DECLARE @TIMER TABLE (T_TEXT VARCHAR(100) NOT NULL, T_TS DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
DECLARE @INT_BUCKET_01 INT = 0;
DECLARE @INT_BUCKET_02 INT = 0;
DECLARE @INT_BUCKET_03 INT = 0;
DECLARE @INT_BUCKET_04 INT = 0;
--SET STATISTICS TIME,IO ON;
RAISERROR(N'--- DRY RUN ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('DRY RUN');
SELECT
@INT_BUCKET_01 = C.CD_ID
,@INT_BUCKET_02 = C.CD_LENGTH
,@INT_BUCKET_03 = C.CD_HEIGHT
,@INT_BUCKET_04 = C.CD_WIDTH
FROM dbo.TBL_CUBE_DIMENSION C;
INSERT INTO @TIMER(T_TEXT) VALUES ('DRY RUN');
RAISERROR(N'--- SCALAR FUNCTION 1 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('SCALAR FUNCTION 1');
SELECT
@INT_BUCKET_01 = dbo.SUDF_CALC_CUBE_VOLUME(C.CD_LENGTH,C.CD_HEIGHT,C.CD_WIDTH)
FROM dbo.TBL_CUBE_DIMENSION C;
INSERT INTO @TIMER(T_TEXT) VALUES ('SCALAR FUNCTION 1');
RAISERROR(N'--- INLINE TABLE VALUE FUNCTION 1 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('INLINE TABLE VALUE FUNCTION 1');
SELECT
@INT_BUCKET_01 = CV.DIMENSION
FROM dbo.TBL_CUBE_DIMENSION C
CROSS APPLY dbo.ITVFN_CALC_CUBE_VOLUME(C.CD_LENGTH,C.CD_HEIGHT,C.CD_WIDTH) CV;
INSERT INTO @TIMER(T_TEXT) VALUES ('INLINE TABLE VALUE FUNCTION 1');
RAISERROR(N'--- CALCULATE IN SELECT 1 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('CALCULATE IN SELECT 1');
SELECT
@INT_BUCKET_01 = C.CD_LENGTH * C.CD_HEIGHT * C.CD_WIDTH
FROM dbo.TBL_CUBE_DIMENSION C;
INSERT INTO @TIMER(T_TEXT) VALUES ('CALCULATE IN SELECT 1');
RAISERROR(N'--- SCALAR FUNCTION 2 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('SCALAR FUNCTION 2');
SELECT
@INT_BUCKET_01 = dbo.SUDF_CALC_CUBE_VOLUME(C.CD_LENGTH,C.CD_HEIGHT,C.CD_WIDTH)
FROM dbo.TBL_CUBE_DIMENSION C;
INSERT INTO @TIMER(T_TEXT) VALUES ('SCALAR FUNCTION 2');
RAISERROR(N'--- INLINE TABLE VALUE FUNCTION 2 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('INLINE TABLE VALUE FUNCTION 2');
SELECT
@INT_BUCKET_01 = CV.DIMENSION
FROM dbo.TBL_CUBE_DIMENSION C
CROSS APPLY dbo.ITVFN_CALC_CUBE_VOLUME(C.CD_LENGTH,C.CD_HEIGHT,C.CD_WIDTH) CV;
INSERT INTO @TIMER(T_TEXT) VALUES ('INLINE TABLE VALUE FUNCTION 2');
RAISERROR(N'--- CALCULATE IN SELECT 2 ---',0,0) WITH NOWAIT;
INSERT INTO @TIMER(T_TEXT) VALUES ('CALCULATE IN SELECT 2');
SELECT
@INT_BUCKET_01 = C.CD_LENGTH * C.CD_HEIGHT * C.CD_WIDTH
FROM dbo.TBL_CUBE_DIMENSION C;
INSERT INTO @TIMER(T_TEXT) VALUES ('CALCULATE IN SELECT 2');
--SET STATISTICS TIME,IO OFF;
;WITH SAMPLE_COUNT(CNT) AS
(
SELECT
COUNT(*) AS CNT
FROM dbo.TBL_CUBE_DIMENSION
)
SELECT
T.T_TEXT
,MAX(SC.CNT) AS SAMPLE_SIZE
,DATEDIFF(MICROSECOND,MIN(T.T_TS),MAX(T.T_TS)) AS DURATION
FROM @TIMER T
CROSS APPLY SAMPLE_COUNT SC
GROUP BY T.T_TEXT
ORDER BY DURATION ASC;
Some results
Method Size Duration
--------------------------------------------------
CALCULATE IN SELECT 1 1000 1000
CALCULATE IN SELECT 2 1000 1000
DRY RUN 1000 1000
INLINE TABLE VALUE FUNCTION 2 1000 1000
INLINE TABLE VALUE FUNCTION 1 1000 2000
SCALAR FUNCTION 2 1000 5000
SCALAR FUNCTION 1 1000 7001
INLINE TABLE VALUE FUNCTION 2 10000 6000
CALCULATE IN SELECT 2 10000 6001
DRY RUN 10000 6001
INLINE TABLE VALUE FUNCTION 1 10000 6001
CALCULATE IN SELECT 1 10000 8000
SCALAR FUNCTION 2 10000 48003
SCALAR FUNCTION 1 10000 49002
DRY RUN 100000 21001
INLINE TABLE VALUE FUNCTION 1 100000 21001
CALCULATE IN SELECT 1 100000 21001
CALCULATE IN SELECT 2 100000 22001
INLINE TABLE VALUE FUNCTION 2 100000 23001
SCALAR FUNCTION 2 100000 395023
SCALAR FUNCTION 1 100000 397023
DRY RUN 1000000 186011
INLINE TABLE VALUE FUNCTION 1 1000000 187011
CALCULATE IN SELECT 1 1000000 190011
INLINE TABLE VALUE FUNCTION 2 1000000 191011
CALCULATE IN SELECT 2 1000000 197012
SCALAR FUNCTION 2 1000000 3857220
SCALAR FUNCTION 1 1000000 3885222
September 26, 2015 at 4:55 am
Eirikur Eiriksson (9/26/2015)
Quick demonstrationTest harness
That's really helpful, thanks.
I've been glibly assuming that scalar functions, returning a single value (rather than table), would be equivalent to doing the calculation inline. I would never have thought to use a CROSS APPLY (unless I needed to use the result of the Function outside the SELECT - e.g. in WHERE or JOIN).
This may just be me being ignorant, of course!, but I wonder if other DEVs assume that too?
It seems to me, as an ex C/C++/APP programmer using ISAM and having graduated to SQL some decades ago, that
SELECT [MyAlias] = dbo.MyFunction(Col1, Col2, Col3)
should be, to all intents and purposes, no different to
SELECT [MyAlias] = Col1 * Col2 * Col3
actually I had assumed that the Optimiser just substituted the function code into the SELECT.
whereas I assumed that a Table Valued Function should be slow because of the abstraction.
What a lot of tosh that assumption has turned out to be :angry: I definitely can't afford for my Scalar one-line in-line UDFs to be 10x slower ... TEN TIMES :w00t: :crying:
I now have to decide what to do about all the simple one-line scalar UDFs I have as "shortcuts" and for "centralised code" ... I'll start a new thread.
Edit: New thread added: http://www.sqlservercentral.com/Forums/Topic1723254-3077-1.aspx
I've run your very helpful test harness (just for 100,000 rows). On repeated runs I get each test having a duration of either 15,62x or 31,25x or 62,4xx (clearly multiples, given a couple or three either side) [then 1,876x,xxx, 2,6xx,xxx]. Some tests are jumping between 15,62x and 31,25x on successive runs. Is there some increased granularity setting I need to use? or something else goofy at my end which is giving me this "banding"?
My DRY RUN test is slow, compared to yours - consistently 62,4xx rather than one of the lower values
These durations on all tests
SCALAR FUNCTION 1 2,6xx,xxx
SCALAR FUNCTION 2 1,876x,xxx
DRY RUN 62,4xx
of the others ("Inline Table Values" and "In Select") it is about 50;50 whether they are 15,62x or 31,25x
Either way I'm really unhappy about the Scalar Function performance 🙁 but at least armed with that knowledge I can do something about it.
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
September 26, 2015 at 5:07 am
I changed your code to allow multiple runs and the output of aggregated average.
I don't know if the changes I have made are "safe" in terms of timing and Statistics Analysis though?
I added these columns to the @timer table
T_SIZE INT NOT NULL,-- Sample size
T_START DATETIME2(7) NOT NULL,-- Start time for the test
Before the first test I added:
DECLARE@LOOPint = 10,-- Number of loops to execute
@SAMPLESIZEint,
@STARTTIMEDATETIME2(7)
SELECT@SAMPLESIZE = COUNT(*)
FROMdbo.TEMP_TBL_CUBE_DIMENSION
--SET STATISTICS TIME,IO ON;
WHILE @LOOP > 0
BEGIN
RAISERROR(N'Loop = %d',0,0, @LOOP) WITH NOWAIT;
I replaced the before / after timers with
SELECT@STARTTIME = SYSDATETIME()
... test here ...
INSERT INTO @TIMER(T_START, T_SIZE, T_TEXT) VALUES (@STARTTIME, @SAMPLESIZE, 'DRY RUN');
and this after the end of the tests for the Loop Decrement and Results
SELECT @LOOP = @LOOP - 1
END-- Loop
--SET STATISTICS TIME,IO OFF;
SELECT
T.T_TEXT
, T.T_SIZE AS SAMPLE_SIZE
, [Min] = MIN(DATEDIFF(MICROSECOND, T.T_START, T.T_TS))
, [Avg] = AVG(DATEDIFF(MICROSECOND, T.T_START, T.T_TS))
, [Max] = MAX(DATEDIFF(MICROSECOND, T.T_START, T.T_TS))
FROM @TIMER T
GROUP BY T.T_TEXT, T.T_SIZE
ORDER BY Avg ASC;
SELECT[@@Version] = @@Version
My results over 10 loops
T_TEXT SAMPLE_SIZE Min Avg Max
----------------------------- ----------- ----------- ----------- -----------
CALCULATE IN SELECT 1 100000 15623 18750 31251
CALCULATE IN SELECT 2 100000 15623 20313 31251
INLINE TABLE VALUE FUNCTION 2 100000 15624 24998 46876
INLINE TABLE VALUE FUNCTION 1 100000 15623 26562 31252
DRY RUN 100000 15624 32813 62502
SCALAR FUNCTION 2 100000 1875015 1968765 2421870
SCALAR FUNCTION 1 100000 1859387 2040640 2640646
September 28, 2015 at 3:04 pm
Kristen-173977
I do really need today's data if possible. They need to be able to view the data, act on it and then refresh to make sure what they have done is correct.
I have written a similar report at other colleges and could run the whole thing in real-time with it taking only a few seconds.
To try to make it work (even though I don't agree with no lock) I added no lock throughout and re-wrote it to use only table-valued functions and it took an hour and 20mins. During execution CPU was averaging between 50-100% and other processes were timing out and being suspended. As soon as it finished things returned to normal.
Thanks for everyone's comments on table-valued functions. Like Kristen-173977, coming from a background of coding and Object Oriented Design, I thought performing a calculation as part of a scalar function would be the same as doing it inline but now I see that is not the case. I wonder why they can't make scalars more efficient!
So that's one more dev that assumed it as well! I would have also assumed that returning a whole table rather than a single value would have been slower.
Whilst everything on my server is almost stationary, I can confirm that table-value functions are faster.
TempDB Splitting
TempDB has now been split into 8 separate files, each 20GB. This seemed to be the recommended way to set it up so there was 1 for each core.
It doesn't really seem to have made any difference though so it's back to the drawing board.
Virtualisation
The server is virtualised on a VMWare host and I do wonder if something is wrong there (and I have exhausted other avenues I think). I did discuss moving to a physical server with IT but they decided against it as they can quickly fall it over to another physical host if things go wrong when virtualised and don't have the spare hardware for an SQL Server cluster.
Is there anything else I can check such as:
* If the throughput from the SAN is insufficient
* If something is not configured correctly on VMWare which could be causing issues
I need to do further testing with the indexes still as well on the test server.
Thanks
Robin
September 29, 2015 at 2:03 am
robinwilson (9/28/2015)
VirtualisationThe server is virtualised on a VMWare host and I do wonder if something is wrong there
I don't know anything about the hardware side ... but we have had clients with SQL on VM where performance has been dire. Typical experience for me is doing stuff during "Config" phase, i.e. I'm the only person connected to SQL. At times there would be a "hesitation" when doing something straightforward. For example, a simple CREATE TABLE would take 15 seconds or more. Next one would be instant. I never put my finger on what was wrong with the implementation, but every VM server where I have experienced that sort of "hesitation" has proved to be a performance nightmare once in production.
IT-literate colleagues say that you have to spend good money on SAN to get any sort of half decent performance, cheap ones just don't hack it. But I don't know if that is the problem I encountered.
I did discuss moving to a physical server with IT but they decided against it as they can quickly fall it over to another physical host if things go wrong when virtualised and don't have the spare hardware for an SQL Server cluster.
Can you stick it on a dedicated machine as an experiment? A laptop (and SQL Express, if the DB not too big) might do - if it performs well there then wouldn't that suggest that the VM framework is not suitable?
Could you survive with Log Shipping rather than clustering? Perhaps you could log-ship to a VM so that that could be "put anywhere" easily enough, but still give you a dedicated box (when not broken 😉 )
September 29, 2015 at 3:34 am
robinwilson (9/28/2015)
TempDB SplittingTempDB has now been split into 8 separate files, each 20GB. This seemed to be the recommended way to set it up so there was 1 for each core.
It doesn't really seem to have made any difference though so it's back to the drawing board.
No, that's not the recommended way. Splitting TempDB alleviates contention on the allocation pages in TempDB. If you don't have contention on the allocation pages, then splitting TempDB into multiple files won't do much. Even if it did, you wouldn't be seeing order-of-magnitude improvements.
One file per core is a old recommendation that's no longer a good idea with the massive core counts these days. I usually suggest 4 files as a starting point.
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
September 29, 2015 at 8:23 am
robinwilson (9/28/2015)
To try to make it work (even though I don't agree with no lock) I added no lock throughout and re-wrote it to use only table-valued functions and it took an hour and 20mins. During execution CPU was averaging between 50-100% and other processes were timing out and being suspended. As soon as it finished things returned to normal.
This sounds like a hibernating / green cpu issue.
Will you confirm that power management for the VM host is disabled in the BIOS and also confirm that the power config within windows is set to "High Performance" instead of the default of "Balanced".
Here is a quick article on that http://bit.ly/XETreehugger
And yet another
http://www.sqlsolutionsgroup.com/sql-servers-hidden-go-fast-button/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 2, 2015 at 4:47 pm
Hello All
I just wanted to update this topic after completing some testing.
Virtualisation
We set up a new physical server and restored a copy of the database there to see if it made any difference and the database performed in the same slow way as it did on a virtual server. The re-written query using only table-valued functions took an hour to run.
Primary Keys and Clustered Indexes
Adding primary keys with clustered indexes on the ID field (as I would normally in my own systems) caused the same query to take just 35 seconds to execute. This is just 1% of what it was before. Also the number of read operations dropped from 898 million to 42 million.
I have now tested adding primary keys to both a physical and virtual sever and experienced the same massive performance boost.
The challenge now is going to be getting the software supplier to agree that their design is flawed and to either continue to support us if we add primary keys and clustered indexes ourselves or preferably add them themselves.
If not we may need to drop this supplier and go with someone who understands relational databases!
Everyone gets constant timeouts in the software currently so it really isn't fit for purpose.
I can't believe the issue turned out to be something so fundamental.
Thanks for all the help from everyone who has helped me to reach the solution.
Robin
October 3, 2015 at 6:13 pm
898 million page reads? Each page is 8'000 bytes... :w00t:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 46 through 60 (of 70 total)
You must be logged in to reply to this topic. Login to reply