Wednesday, May 8, 2013

[T-SQL] filtere index benefit

[T-SQL] filtere index benefit

filtere index benefit

Posted: 07 May 2013 04:07 PM PDT

i need to create a query on a table where the condition is such that we select the rows where the value of a particular column (IS NULL OR =' '). please help me with the idea if the filtered index concept can be of any benefit here ...i tried using it but i got no improvement in the execution plan..

Partition Results

Posted: 07 May 2013 02:00 AM PDT

There is a heap like the one shown below:[code="sql"]USE tempdb;CREATE TABLE Heap ( [Column 0] NVARCHAR(100) );INSERT Heap ([Column 0])VALUES ( '01 Hdr' ) ,( 'Command(s) completed successfully.' ) ,( 'Create Debug Section' ) ,( 'New Query' ) ,( 'Splendid. Try Me!' ) ,( '01 Trl' ) ,( '02 Hdr' ) ,( 'Command(s) completed successfully.' ) ,( 'Create Debug Section' ) ,( '02 Trl' ); SELECT * FROM Heap;[/code]This data is from a flat file imported to an extract (staging) table.I'm trying to write a query that return the following result:[code="sql"]CREATE TABLE Result([Column 0] NVARCHAR(100), PartitionId INT )INSERT Result ([Column 0], PartitionId)VALUES ( '01 Hdr', 1 ) ,( 'Command(s) completed successfully.', 1 ) ,( 'Create Debug Section', 1 ) ,( 'New Query', 1 ) ,( 'Splendid. Try Me!', 1 ) ,( '01 Trl', 1 ) ,( '02 Hdr', 2 ) ,( 'Command(s) completed successfully.', 2 ) ,( 'Create Debug Section', 2 ) ,( '02 Trl', 2 ); SELECT * FROM Result[/code]Hdr and Trl stands for header and trailer respectively.We need to use Substring([Column 0], 4, 3) to determine if it's a header or trailer and match first two characters between header and trailer to find a partition.I appreciate your help. Please let me know if I need to better explain my question. Thanks.

Query seems to be constrained by single core CPU in multiple core server - any suggestions?

Posted: 07 May 2013 08:19 PM PDT

I receive data from an external data provider which comes in as a single large table. The data represents balance sheet information for several different companies. The table structure boils down to 3 columns: 1. PK Company identifier (each different company has a different id number)2. PK Field id number (each item on the balance sheet has a different id number, so total assets might be '15', total liabilities '23' etc.)3. The data itselfFor my OLAP system, I want to populate a table that contains a single row for each company. This table will be viewed regularly by users. The columns in the table would hold all the different balance sheet items for each company.The SELECT statement I used for inserting data from the large source table to my destination table joins up on itself using the different field numbers as follows:[code="sql"]SELECT MySelectListFROM SourceTable aLEFT JOIN SourceTable a1ON a.FieldIDNumber = '1' AND a.CompanyIdentifier = a1.CompanyIdentifier AND a1.FieldIDNumber = '2'LEFT JOIN SourceTable a2ON a.CompanyIdentifier = a2.CompanyIdentifier AND a2.FieldIDNumber = '3'LEFT JOIN SourceTable a3ON a.CompanyIdentifier = a3.CompanyIdentifier AND a3.FieldIDNumber = '4'LEFT JOIN SourceTable a4ON a.CompanyIdentifier = a4.CompanyIdentifier AND a4.FieldIDNumber = '5'[/code]This select statement takes a very long time to run. I tried to figure out what the bottleneck is and I think I'm CPU constrained for the following reasons:1. Pattern of system resource use while query is running:At the very beginning of the query execution, I see heavy reads on the database hard disks as the source table is loaded into memory, which I expected. Then for the vast majority of the time the query is running, hard disk read/write goes to zero and a single core on the server is 100% utilized. There is an occasional brief read of the database hard disk, maybe once every 20 minutes or so for about 2 or 3 minutes. The utilization of the remaining 5 cores is close to 0. Note that I have max degree of parallelism set to zero so all the 6 cores should be available to SQL server.Previously on the same machine, I had hyper-threading enabled. Back then when I ran the same query, only 1 of the 12 threads was fully utilized. So CPU utilization went up when I disabled hyper-threading, but only until the point where 1 core was 100% utilized. 2. If I'm reading diagnostic checks from Glenn Berry correctly (and my correct reading isn't a given because I'm self-taught in all things programming), I don't think I'm memory constrained:When I run this code:[code="sql"]-- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things)SELECT physical_memory_in_use_kb,locked_page_allocations_kb, page_fault_count, memory_utilization_percentage, available_commit_limit_kb, process_physical_memory_low, process_virtual_memory_lowFROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);[/code]The result is 0 for process_physical_memory_low and 0 for process_virtual_memory_low.When I run this code:[code="sql"]SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb, system_memory_state_descFROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);[/code]system_memory_state_desc says 'Available physical memory is high'When I look at the top wait types for the server, I see that the top wait type for the server is CX_PACKET, which is what the server is waiting on over 99% of the time.I would be grateful if anyone had suggestions on how to improve the speed of execution, or could answer the following related questions:1. My primary key on the source table includes both the company Id and the field ID number. Each of the joined tables has a different field ID number. Would the query run faster if I placed a non-clustered index on the field ID number, despite the fact that it is part of the primary key?2. Would the following result in higher CPU utilization?-- Use one statement to join tables a1 and a2 to table a into a temporary table.-- Use a second statement running concurrently to join tables a3 and a4 to table a into a second temporary table. Perhaps this would utilize a different core to the first statement?-- Use a third statement once the first two have finished to join both temporary tables together and insert into my destination table.

LEFT and RIGHT of Delimiter

Posted: 07 May 2013 08:48 AM PDT

I have a value that is '0111~Group Name' in a column called GROUP_NUM in a table called TEMP_TABLEI need to know how to select everything LEFT of the ~ and then RIGHT of the ~, but not show the TILDE. SELECT LEFT VALUE, RIGHT VALUE FROM TEMP_TABLE...I tried..., but it failed. Not enough arguements. Thoughts, suggestions, references?[code="sql"]SELECT LTRIM(RTRIM(SUBSTRING([tt].[GROUP_NUM], CHARINDEX('~')))) FROM [dbo].[TEMP_TABLE] AS tt[/code]

Why XML Datatype present in SQL Server.

Posted: 07 May 2013 02:17 PM PDT

Hi all experts,Every now and then i am learning something new in SQL Server. Latest in that series is XML Datatype. While learning XML datatype i came to know that they are used to stored the XML Document, but being a newbie its hard for me to relate this to a real life scenario where we would be using it. Like when we have to stored number its int, for text its nvarchar, we do have datatype for storing the image also. But really can't get storing XML Document! :w00t:Could you guys please help on this.

Syntax to call a function correct in one databse but not another? (both SQL 2008 R2)

Posted: 07 May 2013 10:45 AM PDT

Hi All I have a function that works in one database but not another. Both databases are SQL2008 R2 64bit.The function: (taken from [url=http://stackoverflow.com/questions/11018076/splitting-delimited-values-in-a-sql-column-into-multiple-rows]here[/url]: ) [code="sql"]CREATE FUNCTION dbo.SplitStrings( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255))RETURNS TABLEAS RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number), Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number))) FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number) WHERE Number <= CONVERT(INT, LEN(@List)) AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter ) AS y);GO[/code]In both cases the function has been created by me in the dbo schema. I can call and successfully execute the function directly with:[code="sql"]SELECT * FROM [dbo].[SplitStrings] ('ABC;DEF',';')[/code]The syntax for calling the function is:[code="sql"]SELECT t.id, t.[priority], f.item FROM dbo.t1 AS t CROSS APPLY dbo.SplitStrings(t.[priority], ';') f[/code]In my test database it works perfectly. but on my server i get: Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.Same function, same user, same test table, same syntax. One works one doesn't... :angry:Can anybody suggest why it works in one case but not another?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog