Solved

sysprivs

Posted on 2011-02-25
12
779 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:anushahanna
  • 7
  • 5
12 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34982987
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34983102
in 2005, please run

select * from sysindexes where OBJECT_NAME(id) = 'sysprivs'
0
 
LVL 39

Expert Comment

by:lcohan
ID: 34983140
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34983320
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
 
LVL 39

Expert Comment

by:lcohan
ID: 34983477
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
 
LVL 39

Expert Comment

by:lcohan
ID: 34983539
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
Highfive + Dolby Voice = No More Audio Complaints!

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.

 
LVL 39

Expert Comment

by:lcohan
ID: 34983623
here's bit more insight still valid in SQL 2008 about tablevariables:

http://support.microsoft.com/kb/305977
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34986035
>>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
 
LVL 39

Expert Comment

by:lcohan
ID: 34997767
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35008786
both are 90.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 35011311
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
 
LVL 6

Author Comment

by:anushahanna
ID: 35150252
very wise inputs- thanks lcohan..
0

Featured Post

How your wiki can always stay up-to-date

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

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now