Solved

sysprivs

Posted on 2011-02-25
12
797 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 40

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 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 40

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 40

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
 
LVL 40

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 40

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 40

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 30
Stored Proc - Rewrite 42 61
SQLCMD Output to file from xpcmd_shell 6 20
Stored Procedure needs owner to execute 5 24
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 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