Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for March, 2011

concatenate string value in SQL

Posted by Simon Cho on 03/08/2011

Hi all,

Have you ever need a concatenation string function for group by clause?

Here is the solution.

http://www.projectdmx.com/tsql/rowconcatenate.aspx

And I like this process.

Ex)

set nocount on;

if OBJECT_ID(‘test’) is not null

drop table test

go

create table test

(

i int identity(1,1)

,c varchar(255)

)

go

insert into test (c)

select ‘a’

union all

select ‘b’

go 100

select i%2, replace(replace(max(b.list),,),,) as sum_c

from test a

cross apply (

select c + ‘,’ as c

from test

where i%2 = a.i%2

for xml path()

) b (list)

group by a.i%2

go

 

Advertisements

Posted in Common | Tagged: , , , | Leave a Comment »

First article

Posted by Simon Cho on 03/07/2011

Hi all,

This is my first blog for publishing SQL article.

I’ll update SQL stuff here.

Thanks.

Posted in Common | Leave a Comment »

MSDN Technical Articles for SQL servers.

Posted by Simon Cho on 03/07/2011

http://technet.microsoft.com/en-us/library/bb545450.aspx

<SQL 2008 R2>

http://technet.microsoft.com/en-us/library/bb418445%28SQL.10%29.aspx

<SQL 2008>

http://technet.microsoft.com/en-us/library/dd631815%28SQL.10%29.aspx

<SQL 2005>

http://technet.microsoft.com/en-us/library/ee229559%28SQL.10%29.aspx

http://technet.microsoft.com/en-us/library/ff928326%28SQL.10%29.aspx

Posted in Common | Tagged: , , | Leave a Comment »

all information about SQL “Memory To Leave” area (also called MTL or “Memory To Reserve”).

Posted by Simon Cho on 03/07/2011

The Database Administrator’s Guide to the SQL Server Database Engine .NET Common Language Runtime Environment
http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm

If you use linked server queries, you need to read this….
http://blogs.msdn.com/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx

How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values
http://blogs.msdn.com/psssql/archive/2009/05/15/how-it-works-dbcc-memorystatus-locked-pages-allocated-and-singlepageallocator-values.aspx

I had log backup jobs setup which started to fail intermittently for last couple of weeks. The SQL server complained of “insufficient system memory”. The jobs were backed up as part of maintenance plan – maxtransfersize option
http://www.sqlservercentral.com/Forums/Topic362968-24-1.aspx

How to find who is using / eating up the Virtual Address Space on your SQL Server
http://blogs.msdn.com/sqlserverfaq/archive/2010/02/16/how-to-find-who-is-using-eating-up-the-virtual-address-space-on-your-sql-server.aspx

VirtualAlloc Function
http://msdn.microsoft.com/en-us/library/aa366887%28VS.85%29.aspx

SQL Server encounters memory pressure and generate 701 Error due to incorrect configuration of Service Broker
http://blogs.msdn.com/sqlserverfaq/archive/2010/03/25/sql-server-encounters-memory-pressure-and-generate-701-error-due-to-incorrect-configuration-of-service-broker.aspx

SQLOS’s memory manager and SQL Server’s Buffer Pool
http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

FAIL_VIRTUAL_RESERVE
http://www.sqlservercentral.com/Forums/Topic432976-360-1.aspx

Fix for “Failed Virtual Allocate Bytes” error? – Service Broker problem
http://social.msdn.microsoft.com/Forums/en/sqlservicebroker/thread/1d527f9e-5497-4e02-a2b2-f0ee3a386326

FIX: Error message when you use SQL Server Service Broker in SQL Server 2005: “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 589824”
http://support.microsoft.com/default.aspx?scid=kb;en-us;959007

INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/kb/271624

Server Memory Options
http://msdn.microsoft.com/en-us/library/ms178067.aspx

Various memory errors are logged to SQL Server error log when using SQL CLR objects
http://support.microsoft.com/kb/969962

FIX: The memory usage of a SQL Server service increases quickly when you run a query that uses a linked server in SQL Server 2005 or in SQL Server 2008
http://support.microsoft.com/kb/971622

AppDomain marked for unload due to memory pressure- Jonathan Kehayias

http://social.msdn.microsoft.com/Forums/en/sqlnetfx/thread/e5ca2988-df87-4ce4-8fb7-b338a81a390e

help with this AppDomain event- Jonathan Kehayias

http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/cc1b3e43-0db8-4e75-b5ab-bc2a4c93b12b/

SQL Server 2005: CLR Integration

http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx

Using the SQL Server Service Startup Options
http://msdn.microsoft.com/en-us/library/ms190737.aspx

Come on 64bit so we can leave the mem…. – Bob Ward

http://blogs.msdn.com/b/psssql/archive/2009/08/26/come-on-64bit-so-we-can-leave-the-mem.aspx

 

Simon Cho

Posted in Common | Tagged: , , , , | 2 Comments »