Which will perform better?
(4) 550Mhz Xeon with 1MB cache and 2GB of RAM
(2) 550Mhz Xeon with 2MB cache and 2GB of RAM
(2) 550Mhz Xeon with 1MB cache and 4GB of RAM
Thanks,
John Fletcher
I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables:
<sqlCacheDependency enabled="true">
<databases>
<add name="MyDb"
connectionStringName="MyDbConnnectionString"/>
</databases>
</sqlCacheDependency>
I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error:
The database 'MyDB' is not enabled for SQL cache notification.To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.
Are the docs I read wrong or am I missing something?
I have a virtual server (VMware ESX) with 64GB RAM running a single instance of SQL 2012 SP1. The max memory config is set to 59392 (58GB).
The Page Life Expectancy for this server has been averaging well under 10 mins for the last few days, according to our monitoring.
I have been checking the amount of data in the buffer cache periodically during the day with the below query, which seems to show that there is never more than about 10GB of data at any one time, frequently dropping below 5GB:
SELECT COUNT(*) AS BufferPages,
CONVERT(decimal(10, 2), COUNT(*) / 128.0) AS BufferMB
FROM sys.dm_os_buffer_descriptorsWhy would the amount of cached data be so low (and cause so much churn)?
I am aware that other things will require some of that memory (plan cache etc.) but with Max Mem of 58GB, I would expect there to be a much higher amount of actual cached data at any one time. I did the same checks on another VM with the same amount of RAM/Max Mem setting, and there was 50GB of data in the cache, with PLE measured in hours.
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €śDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €śdbo€? to be able to flush procedure and data cache without being elevated to €śsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
I'm very confused about which dbms to use. I've two choices: MS SQL Server 2000 and Intersystems Cache. Although Cache is post relational I don't know anything about it. However it has object oriented features, I know how to do things in SQL Server.
Can anyone give me the advantages and disadvantages of both systems in this situation?
Thanks in advance.
Hi all,
We need few clarifications with regard to the caching in SQL Server.
1. When does the SQL Server clears the CACHE or at which event the CACHE gets cleared (specifically execution plans)?
2. Is there any way to retain the execution plans in the CACHE without clearing them ?
3. Can we rely upon the SQL Server CACHE for better performance or the system? We have a stored procedure which takes 25 sec and when the execution plan is cached it takes only 5 sec. So can we rely on execution plan caching in the production environment?
Thanks & Regards,
Hari Haran Arulmozhi
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. Â My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.Â
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
Hi I am using SQL Server cache invalidation with caching in my application.
I have a master page and several other pages that are referring the master page.
I have specified :
<%@ OutputCache Duration="60" VaryByParam="*" SqlDependency="CommandNotification" %> on one of my content page.and I have included the
System.Data.SqlClient.SqlDependency.Start(connectionstring) in my Application_Start.My web.config contains this section as well -
<caching><sqlCacheDependency enabled="true">
<databases><add name="BizPartnerV4" connectionStringName="BizPartnerConnectionString"/>
<add name="DirectBuyBeaverton" connectionStringName="DirectBuyBeavertonConnectionString"/></databases>
</sqlCacheDependency>
I</caching> have also run the aspnet_regsql utility to enable sqlcache dependency for the database.
But my page is not taking the values from cache when the page is refreshed.
Please help.
If I simply want a db table to have caching enabled and just care about the application getting that data from the cache instead of physically getting it from the table (no need for me to have a delegate for notification), there is no need for me to create a sqldependency object within my middle tier method associated with my command, correct? I have the sql cache enabled for a particular db table and have the following in my Application_Start event of the global.asox file (SqlDependency.Start “connectionstring�) Once I change something in the table, the cache is invalidated. That’s all I need, correct?
we r doing some application and SQL server as backend DB Server. this sql server has to be updated continiously 24*7 with data from 3 external sources Cache DB, Iformix and Oracle DB servers. How to handle this in such away that all changes at 3 external sources to be updated to SQL server and this data will use by our application at frondend.
we though of ODBC., but ODBC is very very slow. It should lke Shadowing.
How to handle this?. any help will greatly appreciated.
Can I use SQL_Mobile as PC desktop server or PC local cache server?
I want many data to be cached at user's PC,
and call for this data to be created transparently as for remote
main SQL server.
Is it possible to do without creating complex structure of doubling functionality
of main queries to single SQL server ?
some articles, samples, links, keywords ?
thanks you
I have implemented SQL cache dependency to invalidate cache whenever the result of the stored procedure gets changed. The stored procedure is written obeying all the notification rules.
Our problem:
As long as we are working on Local machine, Development server or Pre production server its working fine means the cache gets invalidated whenever there is any change in the result set. But the same implementation doesn’t work in Production. Cache does not get invalidated.
Difference in Production environment and other environment:
Production uses cluster SQL server, others use standard SQL server. Production has load balancing means application is deployed on 6 servers which hits same clustered SQL server. In application’s global.asax file i have Start dependency so I see 6 different notifications started in SQL server which is right. But whenever there is a change in result set the cache doesn’t get invalidated in any of the application.
My question is
Do we have to follow any different kind of implementation of SQL cache dependency for web farm and cluster SQL server scenario?
On the same note I would like to add, on the same SQL server I have one more database and a different application is accessing this database. This application is also using SQL cache dependency and its working fine. The only thing this .Net application is NOT deployed on web farm. Its deployed on single application server.
SUB: pushing data to SQL server from Informix, Oracle and Cache DB
we r doing some application and SQL server as backend DB Server. this sql server has to be updated continiously 24*7 with data from 3 external sources Cache DB, Iformix and Oracle DB servers. How to handle this in such away that all changes at 3 external sources to be updated to SQL server and this data will use by our application at frondend.
we though of ODBC., but ODBC is very very slow. It should lke Shadowing.
How to handle this?. any help will greatly appreciated.
Thanks in advance.
Prasad
vsrprasad16@gmail.com
Can I view the log cache size in SQL Server memory any DMV's which states that.
View 9 Replies View Related
we r doing some application and SQL server as backend DB Server. this sql server has to be updated continiously 24*7 with data from 3 external sources Cache DB, Iformix and Oracle DB servers. How to handle this in such away that all changes at 3 external sources to be updated to SQL server and this data will use by our application at frondend.
we though of ODBC., but ODBC is very very slow. It should lke Shadowing.
How to handle this?. any help will greatly appreciated.
Thanks in advance.
Prasad
vsrprasad16@gmail.com
I'm trying to set enable SQL cache invalidation on a webpage, specifically on a a datasource that fills a GridView.
The website http://beta.asp.net/QUICKSTART/aspnet/doc/caching/SQLInvalidation.aspx makes it sound so very simple, and I have attempted to do all that it says, yet something just isn't right. The site says that the following needs to be done.
Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.
I've done items 1,2 & 3, but when I attempt to do item 4 I get the following result in TSql:
Msg 15151, Level 16, State 1, Line 1
Cannot find the service 'SqlQueryNotificationService', because it does not exist or you do not have permission.
My login account has sysadmin priviledges, so I'm guessing the latter part of the error doesn't apply to me. But what do I have to do to get SqlQueryNoticationService to exist?
I'm new in the DBA position recently aquired, and I'm having a few issues with MSSQL configuration and administration.
1. I have created a two maintenance plans, one for system databases and another one for user databases, this plans include things such as, Integrity Checks, Optimizations and DB Backups, however the Integrity Checks fail with the error that the DB's must be in single user mode, how can I go about to make this and what are the implications of doing so?
2. There's been a few reports of MSSQL server compromises through UDP port 1433, so I closed it at firewall level, only TCP 1433 is going through, do I really need UDP 1433?
Thank you very much.
Hi All,
I have a query. I want to use sql server authentication
for my asp.net application. I have created a seperate username and
password for the sql server. Now I want to use that username and
password to establish the connection thru web.config. In web.config, I
don't want to give the username and password of sql server. Instead, I
want to get those details in other ways. Please tell me how to handle
the connection string with more security?
Balaji
Greetings,
My connection string looks like this (in the web.config) and is used with many data controls in my application:
<connectionStrings><add name="connection_NAH" connectionString="Data Source=NAH;Initial Catalog=NAH;User ID=xxxx;Password=xxxx" providerName="System.Data.SqlClient" /></connectionStrings>
I'm using vb.net and my code looks like this:Dim myConnection As SqlConnectionDim mySqlCommand As SqlCommandmyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("connection_NAH").ConnectionString)mySqlCommand = New SqlCommand("UPDATE tbMasterOption SET ImageURL = 'test.gif' WHERE ID = 116977")Try myConnection.Open() mySqlCommand.ExecuteNonQuery()Catch ex As Exception Label3.Text = "Couldn't update database record: " + ex.ToString()Finally myConnection.Close() Label3.Text = "Closed db connection"End Try
------------------
The only output I get is from the label3 which says "Closed db connection". Doesn't look like any exceptions are thrown but when I look at the database the record is not updated. Originally I thought my SQLConnection was not constructed correctly but I also tested it by outputting the connection string to Label3 which looked fine. The only other thing I can think of is in the use of ExecuteNonQuery(). Is this the right method or do I need something else? Running the sql query manually works...
Thanks in advance..
I'm using a package configuration to set the value of a user variable which I then use inside my package to build out a certain expression. I got this to work with the XML file option, but I can't get it to work using the SQL Server option.
So this is what Im doing.
(1) I copy pasted the package that works with the XML config option.
(2) In the new package, I deleted the existing XLM config and created a new one, this time choosing the SQL Server option. After I finish, I see that the dbo.SSIS_Configurations table gets created in my database with the value of my variable in there.
(3) I run the package and I see that it works. I'm not sure at this point if it is working because it's reading the value from the config table or from the BIDs environment where I initialized the value.
(4) Anyway, I then go and update the value of my variable in dbo.SSIS_Configurations.
(5) When I run the package again, it doesn't see the new value. What am I doing wrong?
this directory is over 2gb
Program FilesMicrosoft SQL Server100Setup BootstrapUpdate Cache
can you delete the contents safely?
I am using SQL Server 2012 Express.
I am doing performance tuning of SP/Query in Dev-Test environment.
I found that SQL Server caches plan between successive executions.
So if I test/execute SP 10 times, after 1st or 2nd execution, SQL server will pull-up plan-info from CACHE...Not from SQL SERVER Or Database...
Means i am not getting correct answer...
I found this 2 commands:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
But they say that executing above command might interfere/bother other people executing other query/sp on this server.
They also say that: Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance.
Part of query was using Dynamic-SQL executed with EXEC command.
I replaced that with SP_EXECUTESQL.
How can I start testing of each SP-run with Fresh/Blank CACHE ?
I want to analyze procedure cache, to find inefficient plans and parameter issues.
I do it trow DMV But my requests to DMV are very slow and demand resources because procedure cache is about several GB Actually I dont need on-line analysis.
Is it possible to have fast snapshot of procedure cache?
I'm trying to configure ASP.NET2.0 Application to use SQL Server 2000 and yes i did make sure all my setup in configuration manager is setup and the surface area
Whenever i ran the REGSQL .EXE CMD it works until i get to where it suppose to give you a list of data instance to choose from base on ScottGu article in step 4
I'm running SQLEXPRESS ADVANCE
VWD EXPRESS 05
Thanks
Hello, So I am just getting started with .NET, I am using Visual Web Developer 2005 Express andSQL Server 2005 Express on XP Media Center (same as XP Pro) ASP.NET version 2.0.????Anyways, I am having the time of my life figuring out how to connect to SQL Server from within my C#, as follows,(where settings.connection = ".SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;" in my web.config)SqlConnection conn = new SqlConnection(Settings.Connection); SqlDataReader rdr = null; try { conn.Open(); SqlCommand cmd = new SqlCommand("select * from mytable", conn); rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0]); } } the above yields unspeakable errors, namely <font color="red"><b>Cannot open database requested by login. login failed for user 'computername/SQLEXPRESS'</b></font> , etcAND<font color="red"><b>A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)</b></font> If you have ANY information on the suspect, please, please report it to me. Greatly appreciated!Thanks, Cam
View 1 Replies View Relatedany suggestions on having one web config that once put on the dev servers uses the dev sql server and when put on prod will use the production sql server?
would like to encrypt it and be done with it. but it needs to recognize the server it's on.
I have a connection class that does this - but i need to use sqldatasource and not objectdatasource.
Hello,
When I installed MS SQL Server 2000 on our client's computer I installed it under a local account using Windows Authentication as the server logon and am now accessing it using a network login (remoting into the same box). The result is that if I change the password for the local Windows account and reboot the server, SQL server cannot be restarted; I get an error:
A connection could not be established to (LOCAL)
Reason: SQL SERVER does not exist or access denied.
ConnectionOpen (Connect())..
Please verify SQL Server is running and check your SQL Server registration properties and try again.
I've changed the registration properties to use SQL Server Authenticaiton instead of Windows to no avail. Whichever connection option I use the server will not start unless I change the local account's password back to what it was when I installed. This works, but our client wants to have that local account's password changed for security/peace of mind. Any help or advice would be appreciated here :)
Thank you
Hey guys,
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
Chris.
Hey guys,
I have been developing a SQL based data warehouse for financial reporting purposes. I do expect the database to continue to grow over the next few years.
Right now, I am running SQL Server 2000 on an HP DL380 G3 with dual Xeon 2.8GHz, 1.5GB of ECC DDR RAM, 4x 300GB SCSI 10K RPM HDD's.
I would like to replace the server as it is now two generations old and because when there are five people running queries against the data, it starts taking minutes to return the results. When this reporting database hits production, it will be queried by up to 20 people simultaneously and I don't feel the current server will provide results in reasonable amounts of time.
I would like to know what you people think I should be doing to provide an optimal environment for this data warehouse. Should I stick with SQL Server 2000 or move to 2005? Why would you recommend that?
I am thinking of replacing the server with the following configuration: HP GL380 G5, 2x Xeon E5345 Quad-core CPU's, 4GB PC2-5300 ECC DDR2 RAM, 8x 72GB SAS 15K RPM HDD's. Do you think this is an ideal configuration? Too much? Too little?
I would love to hear what you have to say and suggest. Thank you in advance!
Chris.
I'm trying to get a SSIS package to run as a SQL Server Agent job. The package uses a config file. When I try to add the config file to the job under the Configurations tab. When I click on the 'Add' button, I get the following error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The EXECUTE permission was denied on the object 'xp_availablemedia', database 'mssqlsystemresource', schema 'sys'.
The user does not have permission to perform this action.
The statement has been terminated. (Microsoft SQL Server, Error:229)
Any suggestions?
As we know Sql Server 2005 provides its ability to deploy clr assembly on it. However my problem is that my assembly has a configuration file. I used ConfigurationManager.GetSection to create some of the object. When I am deploying the project the trouble is that the app.config is missing. I used a class lib project type to create the assembly. Besides changing a storing method, can I still use this config file to reach my goal? Sqlserver.exe.config is not a good idea and it is not workable. The sqlserverexe host cannot find the assembly files.
Thanks very much!