And is it the same recovery? FULL means more logging than SIMPLE and if they are different it may impact performance. Also tempdb and t-logs location and db maintenance/backups - I can't think of anything

Well according to this the table should be in the Resource database which is hidden to users but we should be at least able to get the object definition from it if not directly query those tables. I still can't find it either and you should probably take this with Microsoft if you really need to query that table.

basically the query is very slow. now this database is in test environment. live db is ok and gives results fast back with temp table or table variable.

so i took a backup of each of the tables and put it in the other server.

so basically, test has its own and production's database (same schema/design) has been restored by a diff name.

and likewise in the other server - the orig prod db and the new restored test database.

and in both servers, the query runs fine on the prod version and not on the test version. the data is being spooned. so it is not a database issue, as both are working of the same xml data defined in the varchar(max) variable.

i am trying to find what system tables are differing between the two databases.

so I got a bunch of them with the attached code.

am in the right track to find out what is the offending party in the test database that the same query will be so slow with a temp table or table variable.

sysprivs is one of the tables that came up with a diff row count, and hence my investigation into it.

Ok - I see now, hoever in my opinion this table won't help you solve the performance issue as I believe is related to privileges.

Here's what I know from my experience working with SQL:

Table variables are not so good on slow systems as they are both in mem and tempdb even if you expect them to be only in mem. Max 100 rows are perferct, up to 1000 on a very good system wityh lots of RAM, NOT virtual machine, and fast tempdb.

You can't index Table variables but just create a PK on creation while temp tables you can index.

I found that using both Table variables and #temp tables in same piece of SQL code is deadly from performance point of view if you have 100+rows in table variabele.

So your original question about performance I would try in dev a paralell identical piece of code but use indexed #temp table instead. Still if production is ok then should be no issue with the code but you could try to tune your DEV to bring it closer to prod assuming in DEV you have usualy less data.

Besides that, I know #temp tables may cause recompilation hoever that may be less expensive in certain cases than long running queries. That comparing to Table variables where no statistics are maintained which means that any changes in data impacting table variable will not cause recompilation of queries accessing table variable.

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

>>I would try in dev a paralell identical piece of code but use indexed #temp table instead.

lcohan, schema wise prod and test are and should be same.

i backup/restored prod database on test server with different name
i backup/restored test database on prod server with different name

and i tested the scripts, in both places, the 'original prod' database worked fast and test database query was slow. the amount of data usage is almost the same (we refreshed the data in test from prod a few weeks ago).

so it seems not like a server issue- because both servers run the query fast in the prod version of the database and not the test... so it seems almost like something is missing in the test or something is in prod that should be in test.. do you see what I am trying to explain? what could it be?

sysprivs was the only one that had more count for prod, and hence started to check on that. the following had more row values (by single digit- only small difference) in test than prod: sysrowsets, sysallocunit, sysqnames, syshobtcolum, sysrowsetcol, sysobjvalues, sysidxstats,
syshobts, sysserefs, sysiscols

And is it the same recovery? FULL means more logging than SIMPLE and if they are different it may impact performance. Also tempdb and t-logs location and db maintenance/backups - I can't think of anything else that may cause different results from seetings/configurables point of view.
If all is identical except data then....that may be the answer because sometimes different data (and cardinality) can cause SQL to create/use different query plans for identical query.

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed