Saturday, April 27, 2013

[T-SQL] output understanding for most memory reads?

[T-SQL] output understanding for most memory reads?

output understanding for most memory reads?

Posted: 27 Apr 2013 12:03 AM PDT

Hi..Top procedures memory consumption per execution as below capture script[code="sql"]SELECT TOP 100 *FROM ( SELECT DatabaseName = DB_NAME(qt.dbid) ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,DiskReads = SUM(qs.total_physical_reads) -- The worst reads, disk reads ,MemoryReads = SUM(qs.total_logical_reads) --Logical Reads are memory reads ,Executions = SUM(qs.execution_count) ,IO_Per_Execution = SUM((qs.total_physical_reads + qs.total_logical_reads) / qs.execution_count) ,CPUTime = SUM(qs.total_worker_time) ,DiskWaitAndCPUTime = SUM(qs.total_elapsed_time) ,MemoryWrites = SUM(qs.max_logical_writes) ,DateLastExecuted = MAX(qs.last_execution_time) FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt GROUP BY DB_NAME(qt.dbid), OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)) TORDER BY IO_Per_Execution DESC[/code]as below outputs, how can understand these numbers? which is normal or critical? Logical Reads means, data read from memory cache is too high number. ( how can reduce? By using proper indexing)output------Memory Reads: 104131 Total IO Reads: 104155 (Executions: 1505IO_Per_execution: 557CPU Time: 215258DiskWaitAndCPUTime: 215272Memory writes: 2Thanksananda

Dynamic export to flat files by date

Posted: 26 Apr 2013 07:42 AM PDT

Hello all,I am tasked with archiving some very large tables. There are about 30 tables that I need to do work on. I need the tables to be exported to pipe-delimited text files. Because of the size of the tables, I also need to split the tables by date to keep the size down. Most of the tables have a date field, the ones that do not are smaller and I can just export them to one file.The tables that have the date fields I need to split out by YYYYMM.Can anyone help me with the best approach to take with this?

Error when Cascade Update on child tables

Posted: 26 Apr 2013 08:31 PM PDT

HiThere is a DB with 4 tables (3 master tables and one is detail which has daily information)Table "a" is master table contains 2 fields:a_code (primary key)a_nameTable "b1" is master table contains 3 fields:a_code (primary key)b1_code (primary key)b1_nameTable "b2" is such as table"b1". b2 is master table contains 3 fields:a_code (primary key)b2_code (primary key)b2_nameTable "C" is detail table contains 5 fileds:a_code (primary key)b1_code (primary key)b2_code (primary key)C_code (primary key)C_nameField "a_code" is primary key in table "a" and also it is foreign key in tables "b1" and "b2".b1_code and b2_code are a part of primary key in their tables and are also foreign key in tables "C".Now we wants create a Relation between tables. So that table C be Related with b1 and b2 then tables b1 and b2 Related with a.In other words, table c is sub table of b1 and b2. And tables b1 and b2 are sub tables of a.In other hands, table a is first level . tables b1 and b2 are in second level and table c is in third level.Now it is necessary select Cascade for update fields.First question:Table c has 2 Relations . one Relation with table b1 and another Relation with b2. But only one Relation can de cascade for update. The second Relation will be faced to error.Error:'b2' table saved successfully'c' table- Unable to create Relationship 'FK_c_b2'. Introducing FOREIGN KEY constraint 'FK_c_b2' on table 'c' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors.Second question:The code fields which are primary keys (a_code, b1_code, b2_code, c_code). They are hidden for user. User can not view them. When you add new record to table , the code field will be creased automatically. on the other hand the field "a_code" not necessary to be changed when you edit field "a_name". therefore it is not necessary to create Relation and also to set Cascade update to child tables. Now I want to ask, is my DB a standard DB in this case?Third question:Field a_name can provide my aim . therefore what is necessary to field a_code? In a standard DB field a_code must be created or not?Thanks very much

Insert statement error?

Posted: 26 Apr 2013 05:35 PM PDT

Hi,what is wrong this code? pl. suggest[code="sql"] create table databaseSize ( DatabaseName varchar (100), RowSizeMB varchar (50), LogSizeMB varchar(50), DBSizeGB varchar (50), SteamSizeMB varchar(50), TextIndexSizeMB varchar(50), time_stamp getdate()) insert into databaseSize ( 'DatabaseName', 'RowSizeMB', 'LogSizeMB', 'DBSizeGB', 'SteamSizeMB', 'TextIndexSizeMB', 'time_stamp') SELECT DB_NAME(db.database_id) DatabaseName, (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB, (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB, (CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024 DBSizeG,(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB, (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB FROM sys.databases db LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id ORDER BY 4 DESC [/code]thanksananda

datetime comparison behaving oddly

Posted: 26 Apr 2013 07:42 AM PDT

I think I'm about to fill a gap in my knowledge base, because I've been banging my head against this all day and I'm baffled. I'm going to provide as simplified a version as possible for illustration purposes.OBS is a table that contains a variety of observations for hospital patients. The field in which the observation is stored is a varchar(2000) called OBSVALUE. This can contain just about anything - in many cases, it contains a date. These are the values I'm interested in.Now for some code:[code="sql"]WITH cte_OBS AS ( SELECT CAST(OBSVALUE AS datetime) AS OBSVALUE FROM OBS WHERE ISDATE(OBSVALUE) = 1)SELECT OBSVALUEFROM cte_OBSWHERE OBSVALUE < GETDATE()[/code]If I execute the contents of the CTE, I get 66350 rows. If I execute the entire thing except for the WHERE clause, I get 66350 rows. If I execute everything including the WHERE clause, I get:[code="plain"]Msg 241, Level 16, State 1, Line 1Conversion failed when converting date and/or time from character string.[/code]The same thing happens if I replace GETDATE() with CAST('4/1/2013' as datetime) or CONVERT(datetime,'4/1/2013'). I'm totally stumped. Can anyone enlighten me?thanks!ron

Nested Stored Procedure does not work as expected

Posted: 26 Apr 2013 07:12 AM PDT

I have 2 Stored Procedures Procedure A and Procedure B.Both Procedures have @year as parameter and if no parameters passed take the current year as an input parameter.Procedure B is called within Procedure ABut Procedure B does not work as expected. ********THERE ARE NO ERRORS when I say PROCEDURE B does not work ********.Everything compiles and executes fine.If the Procedure B is executed as standalone or isolated it works fine and inserts data.But when used in nested mode it works only for Previous year i.e @year-1 for second run , but does not work for current year.It work for current year only when run as isolated or stanalone and inserts data into a table.The syntax for procedure is as belowCREATE PROCEDURE [Procedure A] @Year int = nullWITH EXECUTE AS 'Domain\user'ASIF (@Year is null) BEGIN SET @Year = (Select top 1 year from table where type= 'CurrentYear') END@LastYear = @Year -1 EXEC Procedure B @Year = @Year /* DOes Not work with nested ( no data inserted).Works only when executed as standalone ( data gets inserted) */EXEC Procedure B @Year = @LastYear /* works in nested mode only when the nested SP is ran twice.Also works standalone */THere are many other SP's within Procedure A and all work fine.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog