Showing posts with label time. Show all posts
Showing posts with label time. Show all posts

Friday, March 30, 2012

ICommandText::Execute() causes mem leaks?

Howdy folks!

I'm trying the VS2005 addon Deleaker for the first time, and noticed I got about 40 "heap" memory leaks after the following m_pICmdText->Execute() call. Both m_pICmdText and m_pICmdPrepare are released before exiting the program. What else could cause these leaks? I've narrowed it down to only occuring if the execute statment is called. m_ulNumExecutions is 1 and a_pwszQuery is a CREATE TABLE statement:

// Set command sql query
hr = m_pICmdText->SetCommandText(DBGUID_SQL, a_pwszQuery);
CHKHR(hr, L"Failed to set command text\n", _ExitExeSql);

// Prepare command text for execution
hr = m_pICmdPrepare->Prepare(a_ulNumExecutions);
CHKHR(hr, L"Failed to prepare command text - Invalid Query\n", _ExitExeSql);

for(ULONG i=0;i<a_ulNumExecutions;i++)
{
//Execute query and place results in m_pIRowset
hr = m_pICmdText->Execute(NULL, IID_IRowset, NULL, NULL, (IUnknown **) &m_pIRowset);
CHKHR(hr, L"Failed to execute SQL\n", _ExitExeSql);
}

Thanks!
Jeff

Are you releasing the m_pIRowset variable?

|||Yes, but in this case it's not being used since it was a CREATE TABLE statement. My destructor is fairly thorough, but maybe I'm missing something:

Code Snippet

if(m_pIRowset!=NULL)

{
m_pIRowset->Release();
m_pIRowset = NULL;
}

if(m_pICmdPrepare != NULL)

{
m_pICmdPrepare->Release();
m_pICmdPrepare = NULL;
}

if(m_pICmdText != NULL)
{
m_pICmdText->Release();
m_pICmdText = NULL;
}

if(m_pIDBCrtCmd != NULL)
{
m_pIDBCrtCmd->Release();
m_pIDBCrtCmd = NULL;
}

if(m_pIDBCreateSession != NULL)
{
m_pIDBCreateSession->Release();
m_pIDBCreateSession = NULL;
}

if(m_pISequentialStream != NULL)
{
m_pISequentialStream->Release();
m_pISequentialStream = NULL;
}

if(m_pIUnknownSession != NULL)
{
m_pIUnknownSession->Release();
m_pIUnknownSession = NULL;
}

if(m_pIDBDataSourceAdmin != NULL)
{
m_pIDBDataSourceAdmin->Release();
m_pIDBDataSourceAdmin = NULL;
}

if(m_pIDBProperties != NULL)
{
m_pIDBProperties->Release();
m_pIDBProperties = NULL;
}

if(m_pIDBInitialize != NULL)
{
m_pIDBInitialize->Uninitialize();
m_pIDBInitialize->Release();
m_pIDBInitialize = NULL;
}


The "leak" that I get always has the following stack trace:
MSCVCR80.dll!_calloc_impl Line 94 (f:\sp\vctools\crt_bld\self_x86\crt\src\calloc.c)
MSCVCR80.dll!_calloc_crt Line 61 (f:\sp\vctools\crt_bld\self_x86\crt\src\crtheap.c)

I'm starting to wonder if it's just picking up the allocated space in the sql server engine transaction buffer. I noticed it doesn't give me leaks for SELECT statements.

sql

Wednesday, March 28, 2012

I/O tracking for a Sql Table?

I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp)?

-PatP|||SQL Profiler would be usefull to you !|||Thanks for tip. I'm new to SQL Server 2000, but learning fast! Thanks againsql

Monday, March 26, 2012

I wrote my own configuration tool to maintain the connection strings in a SQL table

I wrote my own VB app to maintain all of my connection strings and link them to packages. I then grab them at run time and set them as variables in memory.Sorry this was suppose to be a reply not a new post.

Friday, March 23, 2012

I want to work out how many hours there is between a start time and a finish time.

I want to work out how many hours there is between a start time and a finish
time.
For example
Start Time: 10:00 am
Finish Time: 11:00 am
Is 1 hour?
How can this be achieved using T-SQL or a stored proc?
Wayne Taylor
www.kryptos.co.uk/blog
On Thu, 14 Oct 2004 14:34:36 +0100, Wayne Taylor wrote:

>I want to work out how many hours there is between a start time and a finish
>time.
Hi Wayne,
SELECT DATEDIFF(hour, @.Datetime1, @.Datetime2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.sqlmonster.com
amit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.c om...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com
|||What type of server are you connecting to in the DTS package?
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
--
Message posted via http://www.sqlmonster.comamit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com|||What type of server are you connecting to in the DTS package?
--
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.droptable.comamit
start on www.sqldts.com
"amit mota via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQ
droptable.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.droptable.com|||What type of server are you connecting to in the DTS package?
Message posted via http://www.droptable.comsql

Monday, March 12, 2012

I TRIED THE PACKAGE AND IT FAILS ALL THE TIME

I think I am missing something, and this something I could achieve with Enterprise Manager. How do I copy one object, lke a table, including primary keys, indexed and triggers, plus data, from one SQL Server to another, or a set of tables? I am at a loss. Of I choose "Copy Data", that is not the same because the table data is copied, but nothing else, not even the primary key gets transferred. So the arriving table is --quite frankly-- useless. What do I do with a table that lacks keys, constraints, indexes and triggers?. On the other hand, I don't see any click-and-shoot way to transfer the missing information for one single or a few tables together. If I script the table, the table gets dropped and recreated, so I lose the data. In the older Enterprise Manager, you cold choose "transfer objects", and everything moved along, data and script, so the arriving table could be used immediately.
Am I going blind or somebody dropped the ball here?

There is a "Transfer Objects Task" in SSIS which does this. I don't know if it gets used by the import/export wizard cos I've never looked at it but the fact that you can't find it probably means that it isn't in which case you'll have to build your own package to do this. Which won't be at all difficult.

-Jamie|||Jamie is correct. The transfer objects functionality does not appear in the wizard, but is available in the SSIS package designer.|||[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

To make the problem more clear: I don't need to transfer logins, just the tables, views, stored procedures, user data types, user-defined functions and defaults.

I think that Microsoft watered down the Enterprise Manager, and is doing exactly what they did with VB6 on Vb.NET, take away the best of the tool: in that case it was change-and-contine, and in the case, the wizards became useless. In any case, I built a package and get the error above all the time, no matter what database I intend transfer from SQL 2000. How do I get around this? I can not start working until I transfer my objects and data, so I need some help here, not just some philosophical advice.
If it takes a lot of steps to achieve what we did with one single wizard, the somebody dropped the ball at Microsof. The "Export Data" should allow the user to select all the details of the transfer. I manage close to 20 SQL Server production machines, and I use that transfer wizard day in and day out.|||Funny you bring this up, we were doing some migrations earlier. I have blogged about it here: http://jason.blogsource.com/

Hope this helps steer you in the right direction...|||I read you blog but I can not guess what the reaso for my error is. Any ideas?|||I was referring to your earlier problem: where you could get tbl structures but not data.... The method I illustrated does add extra steps but is a way if you are desperate.|||

So I assume that you in fact used a package to transfer entire databases from SQL 2000 to SQL 2005 and it worked? you never got the error that I am getting?

Message: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

I wonder what is possibly the matter.

I swear I saw, but exists no more

I have all the installed Control Flow Tasks. At one time I did create a Execute T-SQL Statement task, but after a couple of days when I revisited the project it is still there on my design area, but has disappeared from the Control Flow Tasks group in the toolbox. I did a check of all items. It has disappeared. Any clue(s)? I have posted a screen shot in my blog.

Jay

http://hodentek.blogspot.com/2007/08/strange-behaviour-visual-studio-2005.html

That's because the T-SQL task is under the Maintenance Plan Tasks, not Control Flow Items.

The Execute SQL Task under the Control Flow items, though, is a much better fit long term and will do the same thing as the Execute T-SQL Task.|||

Right you are. Need changing my glasses.

Jay

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.
Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.co...erver_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.co...erver_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.
|||Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
>
|||Hi
You may want to look at
http://www.sql-server-performance.co...recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>
|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson
|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of ws I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.c...server_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.c...server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||
Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
>|||Hi
You may want to look at
http://www.sql-server-performance.c..._recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run
it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to ru
n
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of way
s
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (i
n
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the quer
y
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus doe
s
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.c...server_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.c...server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
>|||Hi
You may want to look at
http://www.sql-server-performance.c..._recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run
it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to ru
n
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of way
s
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (i
n
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the quer
y
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus doe
s
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
reading.
John
"JXStern" wrote:
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||Hi
You may want to look at
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:
>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > Well, can you post some data that we will be able to reprocude the
> > problem/bug?
> >
> > Also, at least, can you show an execution plan of the query?
> >
> >
> >
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> > > Last two or three years now I've been spending a lot of time doing
> > > SQLServer performance tuning. The last couple of weeks I've been
> > > doing this at a place using a whole lot of inline table-valued UDFs,
> > > an issue both in itself and because the database does a LOT of
> > > joining, and in this place, I've been finding one technique is working
> > > for me just a little too often.
> > >
> > > Time and again, there is some complex query that looks like it should
> > > be OK, but is running 100k's of logical reads and taking ten, twenty,
> > > thirty seconds to return a handfull of records. No doubt someone who
> > > can really, really read execution plans might be able to glance at the
> > > plan and see what's wrong, but I haven't reached quite that point yet.
> > > Time and again, what I find is that if I break out a chunk of code and
> > > run it stand-alone in QA, suddenly it runs "correctly", that is, with
> > > 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> > > we understand how much more the optimizer knows with explicit
> > > constants and such, and how SPs have to live with "parameter sniffing"
> > > and such. But I've gone the next step, wrapped ten or twenty or fifty
> > > lines in a relatively small SP or UDF, and darned if it doesn't still
> > > run fast, with all the same parameters and variables that it had in
> > > the original big SP.
> > >
> > > So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> > > large SP, it is not properly resetting the flags or counters or other
> > > internal doodads it uses to compute optimizations.
> > >
> > > Suggestion to speed freaks: break apart large queries inside the SP,
> > > often that works, but when that doesn't work, try moving chunks into
> > > separately compiled SPs, and you may find significantly better
> > > performance falls your way.
> > >
> > > Microsoft, take a look, wouldya?
> > >
> > > J.
> > >
> >
> >
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:
>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).
>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.
>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")
>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.
>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.
>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I receive MSG 7707 when trying to split a partition for the second time. Why ?

Hi

I am trying to implement a sliding window on a table in SQL Server 2005 but i am having some problem.
I have two tables, "Letture" and "LettureStorico". The first one receives data on a few seconds basis, some thousands of rows each day. The second is the historical record and should store all the records till midnigh of two days before, that is, if today is November 21st, LettureStorico stores rows till November 19th 23.59:59.997.

At some time during morning of each day i want to run a stored procedures that takes the records older than midnight of two days before in "Letture" and switch them as a partition in "LettureStorico"

Here's what i do:

/*-*/
CREATE PARTITION FUNCTION [partizioneLive](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION FUNCTION [partizioneStorico](datetime) AS RANGE LEFT FOR VALUES (N'2006-11-15 00:00:00')

CREATE PARTITION SCHEME [schemapartizioneLive] AS PARTITION [partizioneLive] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneLive] NEXT USED [PRIMARY];/*(1)*/

CREATE PARTITION SCHEME [schemapartizioneStorico] AS PARTITION [partizioneStorico] ALL TO ([PRIMARY])
ALTER PARTITION SCHEME [schemapartizioneStorico] NEXT USED [PRIMARY]; /*(1)*/

CREATE TABLE [dbo].[Letture](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_Letture] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneLive(data)
) ON schemaPartizioneLive(data)

ALTER TABLE [dbo].[Letture] WITH CHECK ADD CONSTRAINT [CK_Letture] CHECK (([Data]>='20061115 00:00'))

CREATE TABLE [dbo].[LettureStorico](
[IdLettura] [bigint] IDENTITY(1,1) NOT NULL,
[IdTag] [int] NOT NULL,
[IdGatewayBox] [int] NOT NULL,
[IsEntrata] [bit] NOT NULL,
[Data] [datetime] NOT NULL,
[IsRettifica] [bit] NOT NULL,
CONSTRAINT [PK_LettureStorico] PRIMARY KEY CLUSTERED
(
[Data], [IdLettura] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON schemaPartizioneStorico(data)
) ON schemaPartizioneStorico(data)

ALTER TABLE [dbo].[LettureStorico] WITH CHECK ADD CONSTRAINT [CK_LettureStorico] CHECK (([Data]<'20061115 00:00'))

/*-*/

Every morning i run a stored procedure that, after dropping the check constraints (i'll recreate the at the end), does the following:

/*--*/
SET @.NewBoundary = dateadd(dd,-1, @.dateOfToday)

--this new partition contains the rows i want to switch
ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)
--this new partition is empty
ALTER PARTITION FUNCTION PartizioneStorico() SPLIT RANGE (@.NewBoundary)

--this works fine, rows are moved
ALTER TABLE Letture SWITCH PARTITION 2 TO LettureStorico PARTITION 2

--these two merges lead to two tables partitioned in two partitions each
ALTER PARTITION FUNCTION PartizioneLive() MERGE RANGE (@.OldBoundaryLive)
ALTER PARTITION FUNCTION PartizioneStorico() MERGE RANGE (@.OldBoundaryStorico)

/**/

Till now, everything is working as expected.
Now, when i try to run the same Stored Procedure " a day later" (NewBoundary moved on 1 day) i receive, when i do the "ALTER PARTITION FUNCTION PartizioneLive() SPLIT RANGE (@.NewBoundary)" i receive a 7707 error message:
"Msg 7707, Level 16, State 1, Line 1
The associated partition function 'PartizioneLive' generates more partitions than there are file groups mentioned in the scheme 'schemapartizioneLive'."

How is this possible if i used the "ALL TO [PRIMARY]" and specified which file to use next as in (1) ? Why all this succeeds the first time (when i have 3 partitions) but not the second (again i have just three partitions, i checked) ?

Someone can help me on this, please ?

Many thankx

Wentu

Hello,

I've experienced the same problem. Even if you map all your partitions to the PRIMARY filegroup when you create your scheme, each time before you split your range, you have to call

ALTER PARTITION SCHEME schemapartizioneLive NEXT USED [PRIMARY].

However, why it works the first time is still a mystery to me. Probably when you mal all your partitions, the "next used" is probably also set. As you haven't done it when you split the first time, you got the error the second time.

Greetings,

Adriano