sql server - Extrememly High Estimated Number of Rows in Execution Plan -

-

enter image description here

i have stored procedure running 10 times slower in production in staging. took @ @ execution plan , first thing noticed cost on table insert (into table variable @temp) 100% in production , 2% in staging.

the estimated number of rows in production showed 200 million row! in staging 33.

although production db running on sql server 2008 r2 while staging sql server 2012 don't think difference cause such problem.

what cause of such huge difference?

updated

added execution plan. can see, large number of estimated rows shows in nested loops (inner join) clustered index seek table.

updated2

link plan xml included plan.xml

and sql sentry plan explorer view (with estimated counts shown)

enter image description here

this looks bug me.

there estimated 90,991.1 rows going nested loops.

the table cardinality of table being seeked on 24,826.

if there no statistics column , equality operator used, means sql can’t know density of column, uses 10 percent fixed value.

90,991.1 * 24,826 * 10% = 225,894,504.86 pretty close estimated rows of 225,894,000

but execution plan shows 1 row estimated per seek. not 24,826 above.

so these figures don't add up. assume starts off original 10% ball park estimate , later adjusts 1 because of presence of unique constraint without making compensating adjustment other branches.

i see seek calling scalar udf [dbo].[tryconvertguid] able reproduce similar behavior on sql server 2005 seeking on unique index on inside of nested loops predicate being udf produced result number of rows estimated out of join larger expected multiplying estimated seeked rows * estimated number of executions.

but, in case, operators left of problematic part of plan pretty simple , not sensitive number of rows (neither rowcount top operator or insert operator change) don't think quirk responsible performance issues noticed.

regarding point in comments answer switching temp table helped performance of insert may because allows read part of plan operate in parallel (inserting table variable block this)


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more