sysprivs

http://msdn.microsoft.com/en-us/library/ms179503.aspx
says i should see this in every database.

but none of the following commands gives me anything

select * from sysprivs
select * from sys.sysprivs
select * from mesp.sys.sysprivs
select * from dbo.sysprivs
select * from mesp.dbo.sysprivs
select * from mesp..sysprivs

mesp is user database.

what am i missing?
LVL 6
anushahannaAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
Do you have SQL 2008 R2? I believe that is speciffic to that version and indeed I can't find it in SQL 2005 or 2008 SP2 so must try against SQL 2008 R2 as per BOL

http://msdn.microsoft.com/en-us/library/ms179503.aspx
0
 
anushahannaAuthor Commented:
in 2005, please run

select * from sysindexes where OBJECT_NAME(id) = 'sysprivs'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
lcohanDatabase AnalystCommented:
http://msdn.microsoft.com/en-us/library/ms190940.aspx

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.
 

SELECT SERVERPROPERTY('ResourceVersion');
GO

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sysprivs'));
GO
0
 
anushahannaAuthor Commented:
i'll tell you what the issue is:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_26847599.html

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.
use MES
EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results1(DBName varchar(60),name varchar(128),rows char(11))',
@command1 = 'INSERT ##Results1 SELECT distinct db_name(),CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS ''Table'',CAST(rowcnt as varchar(6)) AS ''#Rows'' FROM sysindexes WHERE indid IN (0,1) AND OBJECTPROPERTY(id, ''IsUserTable'') = 0 GROUP BY id, rowcnt, reserved, dpages ORDER BY ''Table'';'
--,@postcommand = 'SELECT * FROM ##Results1; '
Go 

USE MES_PROD
EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results2(DBName varchar(60),name varchar(128),rows char(11))',
@command1 = 'INSERT ##Results2 SELECT distinct db_name(),CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS ''Table'',CAST(rowcnt as varchar(6)) AS ''#Rows'' FROM sysindexes WHERE indid IN (0,1) AND OBJECTPROPERTY(id, ''IsUserTable'') = 0 GROUP BY id, rowcnt, reserved, dpages ORDER BY ''Table'';'
--,@postcommand = 'SELECT * FROM ##Results2; '
Go 

SELECT distinct * FROM ##Results1 a, ##Results2 b where a.name = b.name and a.rows <>b.rows --and a.rows<b.rows

DROP TABLE ##Results1
DROP TABLE ##Results2

Open in new window

0
 
lcohanDatabase AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
here's bit more insight still valid in SQL 2008 about tablevariables:

http://support.microsoft.com/kb/305977
0
 
anushahannaAuthor Commented:
>>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
0
 
lcohanDatabase AnalystCommented:
Could you check the database recovery and compatibility mode to make sure they are identical?

use your_test_dbname
go
SELECT DATABASEPROPERTYEX (DB_NAME(), 'Recovery')
select cmptlevel from master.sys.sysdatabases where name = DB_NAME()

use your_prod_dbname
go
SELECT DATABASEPROPERTYEX (DB_NAME(), 'Recovery')
select cmptlevel from master.sys.sysdatabases where name = DB_NAME()

0
 
anushahannaAuthor Commented:
both are 90.
0
 
anushahannaAuthor Commented:
very wise inputs- thanks lcohan..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.