Thursday, March 29, 2012

Does SqlDataSource hold the connection ?

If we bind a GridView to a SQLDataSource, is it not a connected usage, which locks up one connection thread ?I don't think so. Once it's databound the connection should be closed. Any sorting or paging will open a new connection.|||No, the connection will be closed. However, you may tie up a connection on the server for a little while if you have connection pooling turned on.

Does SQL2005 still require all FTI keywords to match in the same column?

Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
Hi Thomas,
I am afraid SQL 2005 shows the same behavior. FTS is designed to search for text/documents, which normally are stored in the same column. Can you please explain a bit more why you need to have queries that involve several word matches for several columns?
Thanks!
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:33 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Does SQL2005 still require all FTI keywords to match in the same column?
Hello,
My understanding of full-text index searching is that CONTAINS()
requires that all the keywords supplied (separated by AND) must match
in the same column in order for the record to be considered a match and
returned. So if we have a situation where we need keywords to match in
multiple columns, we either need to use multiple CONTAINS() statements,
use FREETEXT() and allow it to manipulate our keywords, or create a
single column containing all the text we want indexed for a row and
search that unified column.
Is this correct? If so, is this shortcoming fixed in SQL2005?
Thanks,
Thomas
|||Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Hi Thomas,
I see your problem. Let me think about the best solutions.
Most of FTS users are focus in get great functionality to efficiently search inside a document, rather than to search parts in different documents stored in different columns. Anyway, for these cases like yours, we support multiple CONTAINS. Is true that
the performance is not as good as with one single CONTAINS but if we would allow from the beginning to have several columns look ups in a single CONTAINS, we would probably finish with similar performance even if you are just writing one clause.
The good news is the following.
-For next FTS release we have several architecture improvements that will improve dramatically the joined queries. This means that mix relational (date for instance) with FTS search will be efficient. Following your example, before look the FTS side, the
optimizer will get the few ones that pass the date filter and then these ones will be FT searched.
This improvement also will improve multiple CONTAINS queries, so you will not longer experiment pain.
-For now, the best you can do is to use computed columns. These columns will contain virtually the same data than the original columns and you can create a FT index on that column. The indexing time will take longer as you are merging 2 or more columns bu
t at query time you will be able to query efficiently and find what you look for.
Does this help?
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas
Posted At: Wednesday, February 15, 2006 9:34 PM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Hi Fernando,
I'm not sure exactly how to respond... SQL Server excels at storing all
kinds of data. Our records are hybrids of large text fields, numbers,
dates, etc. We have multiple text columns. Just as one example, let's
say we have an email database where we have the headers stored in
different fields, at the very least, subject and body in different
fields. A person enters multiple keywords, and even if some are in the
subject and some are in the body, the record should match. That's just
a basic example.
We were really blown away when we found out this does not behave in
this fashion. It makes no sense, from our perspective as a user, to
query * (all columns) for some keywords, and require they all match in
only a single column.
Another problem we are fighting that lends itself to being allowed to
find the keywords across columns: tagging. We have found that we can
really speed up our searches if the entire search is performed on the
FTS side of the equation.
Contrived Example: You want to find all records that contain the word
"house" and were created in September 2005. Your table holds 100,000
records. Let's say 50,000 of those records contain the word "house."
But only 1000 were created in September 2005. You could do a search
like this:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house"') AND createdate
BETWEEN '09/01/2005 00:00:00' AND '09/30/2005 23:59:59'
But we have found this search is really slow. The slowdown is in the
number of records being returned that contain "house" even though most
of them are not going to pass our SQL Server filter of the date. So we
want to create a tag column of textual things that we can search on the
FTS. Then our query would be:
SELECT * FROM ourtable WHERE CONTAINS(*, '"house" and "DT200509"')
Now we've shifted the date requirement over to the FTS side of the
search. Admittedly, a hack, but it should work. As you know, it
doesn't, because the two keywords will be found in two different
columns.
So what we are forced to do now is the ultimate hack: create a single,
new text field where we are duplicating all of our data from multiple
columns, adding our "tags" for constraining the searches, and then
full-text indexing this single column in order to get fast searches.
It seems weird that FTS is designed to search documents when SQL Server
is designed to hold all kinds of data. Shouldn't FTS be designed to
efficiently search what SQL Server can hold?
Thanks for your time.
Regards,
Thomas
|||Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas
|||Hi Thomas,
When I say next FTS release I mean the next release, not any upgrade. And don’t worry, the next version should be no longer than 2007
In a following post I will let you know the steps to work with computed columns in SQL 2005.
Regards,
Fernando Azpeitia Lopez,
Program Manager
SQL Server FTS team
--Original Message--
From: Thomas [mailto:tomwinzig@.gmail.com]
Posted At: Friday, February 17, 2006 10:50 AM
Posted To: microsoft.public.sqlserver.fulltext
Conversation: Does SQL2005 still require all FTI keywords to match in the same column?
Subject: Re: Does SQL2005 still require all FTI keywords to match in the same column?
Fernando Azpeitia Lopez wrote:

> The good news is the following.
> -For next FTS release we have several architecture improvements that will improve
>dramatically the joined queries. This means that mix relational (date for instance) with
>FTS search will be efficient. Following your example, before look the FTS side, the
>optimizer will get the few ones that pass the date filter and then these ones will be FT
>searched. This improvement also will improve multiple CONTAINS queries, so you will
>not longer experiment pain.
When you say the "next FTS release," does this mean an upgrade to SQL
2005's FTS, or do you mean the FTS that is released in whatever version
comes after SQL Server 2005 (i.e. SQL Server 2010 ;-)

> -For now, the best you can do is to use computed columns. These columns will
>contain virtually the same data than the original columns and you can create a FT index
> on that column. The indexing time will take longer as you are merging 2 or more
>columns but at query time you will be able to query efficiently and find what you look
>for.
Can you give me an example of how to do a FTI on a computed column? We
are currently using SQL Server 2000, and only preparing our move to
2005, so are not yet familiar with 2005 completely.
Thanks,
Thomas

Does SQL2000/MSDE run on XP Tablet?

Hello there,

i have a great doubt: does MSDE run on XP Tablet Edition? I bought a beautiful notebook but didn't notice that it runs XP tablet...

Thanks in advance

Yes it does

Does SQL uses index in the following select statement

I have the following table structure:
PK_Column1
PK_Column2
IndexedColumn
Column_ABC
Column_XYZ
Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
values in the following select statement:
SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
PK_Column1=@.MyParam
When I run this statement it works too slow and I see alot of reads in SQL
Server Profiler.
Is there any way to improve the performance in this case?
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
If Indexed column is clustered, it probably would as it can do a clustered
index range scan.
Else, it may not. It all depends on how up to date the statistics are, the
data types of the columns, how selective the indexes are and the number of
rows.
Show the query plan and we can tell.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Alex via webservertalk.com" <no@.spam.pls> wrote in message
news:58e799d08a0ce@.uwe...
>I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||Thank you for your answer.
Here are more details:
PK_Column1 smallint
PK_Column2 int
IndexedColumn DateTime (NON-CLUSTERED and not unique)
Column_ABC varchar
Column_XYZ varbinary(BLOB)
The table has about 4M rows.

>Show the query plan and we can tell.
How can I get it? I am using the Standard edition of SQL Server 2005.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1|||If there is a clustered index on PK_Column1, then it might do an index
scan on the index of IndexedColumn. However, the query would benefit
more from an index on (PK_Column1, IndexedColumn).
I am not running SQL2K5, but I guess that SET SHOWPLAN_TEXT ON will
probably still work...
HTH,
Gert-Jaqn
"Alex via webservertalk.com" wrote:
> I have the following table structure:
> PK_Column1
> PK_Column2
> IndexedColumn
> Column_ABC
> Column_XYZ
> Does SQL Server 2005 uses the IndexedColumn index to find the MIN and MAX
> values in the following select statement:
> SELECT MIN(IndexedColumn), MAX(IndexedColumn) FROM MyTable WHERE
> PK_Column1=@.MyParam
> When I run this statement it works too slow and I see alot of reads in SQL
> Server Profiler.
> Is there any way to improve the performance in this case?
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1

does SQL use AWE memory whenever possible?

I've got a 32-bit server with 16GB memory. In SQL Server, AWE is enabled. Does SQL use AWE memory for everything possible? I've also got SSAS installed on that same box, and SSAS can't use AWE memory. So I want SQL to use AWE memory as much as possible to leave as much free for SSAS as possible.

As I understand it, when you set the max server memory setting for SQL Server, it prevents the sum of normal and AWE memory from going over that limit you've set. Is there any way to tell SQL server to use as much AWE memory as it wants, but be use as little normal memory as it can?

SQL Server uses AWEonly for data cache pages in the buffer pool. As far as I know this can't be changed (in any supported fashion).

Also, I could be wrong here, but I believe the 3 GB limit is per process, meaning you could have two individual processes that don't support AWE each using 3 GB on a 16 GB system - they wouldn't be limited to a grand total of 3 GB between them. Where AWE comes in is allowing a single process to use more than 3 GB by itself. I'm sure the OS guys can correct me if my assumptions about AWE are incorrect.
|||

We're on the right track to answering my question. Do all pages that it caches use AWE memory exclusively? (Does it use AWE whenever possible?) Will it ever cache pages using normal memory if AWE is on and there is still AWE memory available?

I've put a 1GB limit on SQL so as to leave plenty of room for SSAS. I'm wondering if removing that limit on SQL will cause it to use more normal memory, or just more AWE memory.

I'm pretty sure it's 3GB total across all processes, not per process.

|||Well, there's the buffer cache and the procedure cache. The buffer cache, which holds actual data, will use AWE if it's enabled. The procedure cache doesn't use that, but I don't believe it'll end up nearly as large as the buffer cache under normal circumstances.

As for whether or not non-AWE applications are limited to 3 GB each, or 3 GB total, I think we'll need a more authoritative answer from someone on that, since it has a significant effect on how to handle the situation. This KB article makes me think it's 3 GB each, but I'm not 100% certain: http://support.microsoft.com/default.aspx/kb/283037

To get an idea of how memory is being allocated, compare the Performance Monitor counter SQLServer:Memory Manager\Total Server Memory (KB) to the amount of memory that Task Manager reports (which doesn't include AWE allocations).
|||

If you set max server memory, it limits how much memory, total that the database engine can use, whether it is AWE or not. I have not heard of any way to try to force SQL Server to use AWE memory first.

If you are concerned about memory, and have 16GB of RAM, you should seriously consider running 64-bit, with the "Lock Pages in Memory" right granted to the SQL Server service account. That is assuming your processors are 64-bit capable (which they should be if they are less than about two years old). I have some good DMV queries here:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!526.entry

sql

Does SQL substitute float = 0 with DBNull.Value?

Hi
I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.

I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.

But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.

In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:


if (txtrangefrom.Text != "") {
objQuestion.RangeFrom=float.Parse(txtrangefrom.Text);
objQuestion.RangeTo=float.Parse(txtrangeto.Text);
}
else {
objQuestion.RangeFrom=Convert.ToSingle(null);
objQuestion.RangeTo=Convert.ToSingle(null);
}

And this is what my Business object look like. It sets up the parameters and calls the Data Access Application Block to create an entry in my table:


// fieldslist
float cvintRangeFrom;
float cvintRangeTo;

//properties
public float RangeFrom {
get {
return cvintRangeFrom;
}
set {
cvintRangeFrom = value;
}
}

public float RangeTo {
get {
return cvintRangeTo;
}
set {
cvintRangeTo = value;
}
}

// some code deleted for readability...

public int AddOption() {
string cvstrSpName = "addOption";
SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true);
//lines deleted for readability...
//check if the optional fields have a value associated with them. if not, assign dbnull.value.
cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value);
cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value);
//lines deleted for readability...
SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams);
return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value));
}

I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.

The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!


objQuestion.AddOption();
//txtrangefrom.Text=""; on the next line I test the value in the business object...
txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!!
//txtrangeto.Text="";
txtrangeto.Text=objQuestion.RangeTo.ToString();

So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser...
Any help or ideas will be greatly appreciated...I had this same problem with a VB project.

Just change the value of the floats to something that is not within the valid range (-1 or something) instead of null. It's not pretty or elegant, but it's better than fiddling with nulls ;)

SQLServer (I think) does implicitly convert 0 to null in some occasions, but I don't know the full details.

HTH|||You're right, it's not pretty, but I'll give it a go.
Thanks.

Does Sql server2005 standard edition support Sql server integration services and SSRS

Does Sql server 2005 standard edition support Sql server integration services and SSRS. i saw two conflicting information.

And what is the difference between Integration services with basic transforms and integration services advanced transforms.

Please recommend.

Yes.

The following advanced transformations are available only in Enterprise edition related on SSIS:

Analysis Services Partition Processing Destination - with it you can perform incremental, full, or update processing of a AS partition.

Analysis Services Dimension Processing Destination for loading and processing a dimension.

Data Mining Training and Data Mining Query Component

Fuzzy Grouping, Fuzzy Lookup

Term Extraction and Term Lookup -used for "Text Mining"(Text Data Mining that work good for english texts)

So if you want to use SSIS package to process a cube, data mining structure and crossroad of bad data(Fuzzy Grouping, Fuzzy Lookup) you need Enterprise Edition.

|||

Can we use

SSIS
SSRS
ONLINE PROCESSING USING .NET
BATCH PROCESSING USING .NET

all in the same server. is there any performance issue related to standard 2005 vs enterprise 2005

Thanking you.

|||

In terms of comparing standard and enterprise on same machine , sure no, but you have limits on Standard:

-hardware level : max 4 CPU on server

-software level :NO partitioning, indexed views, and more

An article of this comparison

|||Thankyou