well faisal, either you could http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/ now and then
or look at your queries and try to isolate those hurting your plan cache the most; are they recognizable and the data behind it not too dynamic by nature, build a ‘cache’ for them - either in memory or a Redis kind of thing are they not recognizable or are the data too dynamic (like airplane tickets on popular flights), you could try redesigning the tables or you could pull in larger datasets and reduce in-memory (if your DB is hurt more than your CPU’s) cheers, walt > Den 03/10/2015 kl. 00.20 skrev Faisal Mansoor <[email protected]>: > > Thanks walt, > > Non parametric queries are polluting SQL Server plan cache, which is > affecting overall database performance. > > I understand that parameter count for the IN clause can vary widely, but, > parameterizing these queries will substantially reduce than number of > generated plans. > > > On Thursday, October 1, 2015 at 11:31:30 PM UTC-7, walt wrote: > Fail to see the use case Faisal - plz elaborate - and keep in mind that > > Article.where id: [1,2,3,4,5,6,7,8,9,10,11, . . . , 2100] > > would generate one heck of a parameterized array ;) > > > > Den 02/10/2015 kl. 04.39 skrev Faisal Mansoor <[email protected]>: > > > > Activerecord generates parameterized queries for basic types, but, it does > > parameterize array types. > > > > E.g. > > > > Article.where(id: 1) > > # generates > > # SELECT "articles".* FROM "articles" WHERE "articles"."id" = $1 [["id", > > 1]] > > > > But, > > > > Article.where(id: [1,2]) > > # generates > > # SELECT "articles".* FROM "articles" WHERE "articles"."id" IN (1, 2) > > # rather than > > # SELECT "articles".* FROM "articles" WHERE "articles"."id" IN ($1, $2) > > [["id", 1], ["id", 2],] > > > > Is it possible to restructure the query or use Arel to generate > > parameterized query for IN clauses? > > > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Ruby on Rails: Talk" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [email protected]. > > To post to this group, send email to [email protected]. > > To view this discussion on the web visit > > https://groups.google.com/d/msgid/rubyonrails-talk/3edc1ebe-8d1b-4b42-9229-75934110b26b%40googlegroups.com. > > > > For more options, visit https://groups.google.com/d/optout. > > > > -- > > Denne besked er blevet skannet af > > ALCO Stopspam, og menes at være fri for vira og spam. > > Klik her for at rapportere denne besked som spam. > > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/rubyonrails-talk/6ad9443b-3fd6-4e8c-b934-f8c83760ea26%40googlegroups.com. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/255C3551-05DB-41A0-9FE2-0CC42B14BD7A%40diechmann.net. For more options, visit https://groups.google.com/d/optout.
