I have a table that holds date and time values as INT data type. eg a date is stored as YYYYMMDD, like 20110901 and a time is stored as HHMMSS, like 21826.
The date is close to ISO standard so its not too big an issue however can anyone suggest a simple way to convert to a TIME type please? I have come up with a hideous combination of concatenation, reversing, substring, convert that I am worried will be difficult to read by other staff.
is there an accepted ratio of reads to writes that makes an index worthwhile or is it less cut and dried than that?
I am using this:
WITH UnusedIndexQuery ( Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows )
AS ( SELECT
s.object_id ,
objectname = OBJECT_NAME(s.OBJECT_ID) ,
indexname = i.name ,
i.index_id ,
reads = user_seeks + user_scans + user_lookups ,
writes = user_updates ,
p.rows
FROM
sys.dm_db_index_usage_stats s
JOIN
sys.indexes i
ON
i.index_id = s.index_id
AND s.OBJECT_ID = i.OBJECT_ID
JOIN
sys.partitions p
ON
p.index_id = s.index_id
AND s.OBJECT_ID = p.OBJECT_ID
WHERE
OBJECTPROPERTY(s.OBJECT_ID, 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
),
IndexSizes ( schemaname, tablename, object_id, indexname, index_id, indextype, indexsizekb, indexsizemb, indexsizegb )
AS ( SELECT
sys_schemas.name AS SchemaName ,
sys_objects.name AS TableName ,
sys_objects.[object_id] AS object_id ,
sys_indexes.name AS IndexName ,
sys_indexes.index_id AS index_id ,
sys_indexes.type_desc AS IndexType ,
partition_stats.used_page_count * 8 AS IndexSizeKB ,
CAST(partition_stats.used_page_count * 8 / 1024.00 AS DECIMAL(10,
3)) AS IndexSizeMB ,
CAST(partition_stats.used_page_count * 8 / 1048576.00 AS DECIMAL(10,
3)) AS IndexSizeGB
FROM
sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON
partition_stats.[object_id] = sys_indexes.[object_id]
AND partition_stats.index_id = sys_indexes.index_id
AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
ON
sys_objects.[object_id] = partition_stats.[object_id]
INNER JOIN sys.schemas sys_schemas
ON
sys_objects.[schema_id] = sys_schemas.[schema_id]
AND sys_schemas.name <> 'SYS'
)
SELECT
[IndexSizes].[tablename] ,
[IndexSizes].[indexname] ,
[IndexSizes].[indextype] ,
[IndexSizes].[indexsizekb] ,
[IndexSizes].[indexsizemb] ,
[IndexSizes].[indexsizegb] ,
UnusedIndexQuery.Reads ,
UnusedIndexQuery.Writes ,
CAST(CASE WHEN [Reads] = 0 THEN 1
ELSE [Reads]
END / CASE WHEN [Writes] = 0 THEN 1
ELSE writes
END AS NVARCHAR(8)) + ':1' AS [Benefit Ratio (Read:Write)] ,
UnusedIndexQuery.[Rows]
FROM
UnusedIndexQuery
INNER JOIN IndexSizes
ON UnusedIndexQuery.object_id = IndexSizes.object_id
AND UnusedIndexQuery.index_id = IndexSizes.index_id
ORDER BY
CASE WHEN [Reads] = 0 THEN 1
ELSE [Reads]
END / CASE WHEN [Writes] = 0 THEN 1
ELSE writes
END ,
reads ,
[Writes] DESC ,
[indexsizemb] DESC
to get an idea of the state of the benefit of my indexes.
At the two ends of the results I am clear - 1,000,000 reads and 0 writes = good index to speed up data retrieval, 1,000,000 writes and 0 reads means we are maintaining an index for zero reference.
What I am not certain on is where the activity is shown as more balanced - where do I make the cut and start dropping indexes?
thanks
Jonathan
Scenario
Its pretty involved to explain but I have a procedure that updates data in two databases on instance1 and as part of the process (its a cursor for relevant reasons) it needs to make updates to a database on instance2 where certain criteria are met. If the line of code that references instance2 is altered to be the local instance the procedure runs in less than a minute. If it is set to refer to the correct location on instance 2 the procedure takes 30-40 seconds per record (we have never let it complete).
History (this morning)
Reviewing the estimated execution plan, the code that makes the single record update on instance 2 is using a remote scan on a table that is 100k records deep.
I changed this from
Update C set col1 = @val1, col2 = @val2 where col2 = @ID
to
Execute ('Update C set col1 = @val1, col2 = @val2 where col2 = @ID') as user1 at Instance2
where Instance2 is a linked server and user1 is a sql login that has impersonate enable on the linked server. This was so that the update process can make use of the clustered index on col2 and therefore avoid the table scan.
Issue
We are now getting security/authentication errors and the script is failing with
Msg 15274, Level 16, State 1, Procedure "procname", Line 263
Access to the remote server is denied because the current security context is not trusted.
Can anyone advise me what I need to configure to allow this update to execute please? Or, is there a better way that I can get the update to use the index on the table on Instance2? From my knowledge table hints are not allowed on remote queries...?
Many thanks
Jonathan
I have a SQL 2008 developer edition with SSRS and the report manager is only available from the local machine. If I try to access it from any other machine I get challenged for my domain u/name and pwd 3 times and then the screen stays blank.
I have made changes to some config files (originals copied out) in order to get a 3rd party application to run but that is now uninstalled and the config files are all back to vanilla (originals copied back in)
I feel its something to do with authentication but am stuck ...
any suggestions welcomed
Jonathan
I have a production SQL Server that is set with system databases as Latin1_General_CI_AS but not all user databases are the same, some have been created with SQL_Latin1_General_CP1_CI_AS, and several others - they have been created as part of 3rd party application installs etc.
Running Exec xp_logininfo in these databases causes a typical COLLATION error.
Is there any way around this? (copy and recode xp_logininfo versions for alternate collations? Change all dbs to be same collation and suffer fallout of applications crashing ..?)
all thoughts appreciated.
Edit #1 I am trying to review and eventually move towards managing security and users/logins. Currently if I run :
use DB1
GO
exec xp_logininfo
go
exec xp_logininfo 'domain\groupname'
go
exec xp_logininfo 'domain\username'
go
I get 3 data sets, 1 a list of all users, 2 details of the groups access and 3 how (by virtue of group membership) the specified login gets to access data in DB1
However, if I execute:
use DB2
GO
exec xp_logininfo
go
exec xp_logininfo 'domain\groupname'
go
exec xp_logininfo 'domain\username'
go
I get 3 x 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" ... '
DB1 has collation of Latin1_General_CI_AS
DB2 has collation of SQL_Latin1_General_CP1_CI_AS
all system databases are Latin1_General_CI_AS
hope this explains a bit more...
If anyone knows of a good security audit tool then thats kind of where I am heading. I have two simple questions:
1 - Who can access this database?
2 - What databases can this user get to?